Changing foreign key to be nullable 1

[Olaf Doschke]

I have a table with a foreign key (uniqueidentifier, doesn't matter) and the corresponding referential integrity constraints to always refer to a record in the foreign table, of course not nullable.

The need for that reference is not there anymore, but I don't want to drop the field. In the future it can be set NULL, but I want to keep current references as historical data.

If I use SQL Server Management Studio and manually change the field to be NULL, then generate an sql script the script is huge and actually drops all constraints, recreates a temp table, copies all data to it, etc. to finally drop the old table and rename the new one.

Is there another, simpler chance to make this via a script?

I need a script, because doing it manually on the development server does not help, I either need to do this manually in the productive server too at the time the new application version is rolled out, or - what I usually rather do, I execute an sql script to change the database.

I can see the need to drop the single constraint regarding that one foreign key, but I think this should result in a shorter script than 205 lines, to make a foreign key field nullable and drop it's contraint.

How would you go about this?

Bye, Olaf.

 

[Olaf Doschke]

One more info: This is SQL Server 2005.

And even if I manually drop the constraint on the foreign key field (which is done via the simple ALTER TABLE [dbo].[Tablename] DROP CONSTRAINT [Keyname]), even then the change of the foreign key field to allow null values does result in a lengthy change script. Why?

Bye, Olaf.

 

[Olaf Doschke]

I got it working with

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Constraintname]')
ALTER TABLE [dbo].[Tablename] DROP CONSTRAINT [Constraintname]
ALTER TABLE [dbo].[Tablename] ALTER COLUMN foreignkeyfield uniqueidentifier NULL

The only question that remains is, why does SQL Server Management Studio create such a complex script for that simple change?

Bye, Olaf.

 

[SimonSellick]

I don't know, but you could save the generated script and have a look at it. Often, simple-seeming changes (for example, moving a column) require the table to be saved (data), dropped, re-created and loaded, which obviously generates a fair amount of code. This doesn't seem to be the case in your example, though.

 

[markros]

SQL Server Table Designer often generates needlessly complicated script. That's a given, that's why in most cases it's better to use T-SQL to change tables structures.

PluralSight Learning Library

 

[Olaf Doschke]

Well, Simon,

I did not save but take a rough look of what the script would have done, and it does what you outline, while there is no reason for it. I even did the step to drop the foreign key contraint so the change of that field to be nullable is as simple as to do an ALTER TABLE ... ALTER COLUMN ...

I think you're right, markros. It's simply a weakness of the table designer. While the feature itself is rather advanced the implementation is not ideal. I also have another tool outside SQL Server (Xcase) to manage database meta data (models), convert databases to different servers and generate update scripts from the differences of to model versions. Haven't used that feature there much, as it's an overhead, if just one table is involved, but I may give that a try in the long run. For now I'm fine with the self written three-liner and fell more comfortable I'm not missing a point.

Bye, Olaf.