FROM NULL TO NOT NULL, BUT WITH DEFAULT
informatika
Posts: 18
I have a suggestion for a new option. Sometimes it happens, that we create a table like this:
As you can see, Field2 can be NULL and we also have values in the database which are NULL. But because of new specifications or whatever, we need the table like this:
So, for an update, you need to recreate the table. SQL Server Management Studio makes something like this:
And here is the problem. You cannot insert the values into Tmp_Test, because Field2 does not allow NULL values anymore. But it would be possible, if you would turn the NULL-Value into the Default-Value, for example
I know, it is easy to workaround this, with a simple Update-Command before Updating
But perhaps it is worth some consideration to implement this feature.
Thank you,
Michael Kriegner
CREATE TABLE Test ( ID INT IDENTITY ( 1, 1 ), Field1 INT NOT NULL, Field2 INT NULL ) INSERT INTO Test ( Field1, Field2 ) VALUES( 1, 1 ) INSERT INTO Test ( Field1 ) VALUES( 2 ) SELECT * FROM Test
As you can see, Field2 can be NULL and we also have values in the database which are NULL. But because of new specifications or whatever, we need the table like this:
CREATE TABLE Test ( ID INT IDENTITY ( 1, 1 ), Field1 INT NOT NULL, Field2 INT NOT NULL DEFAULT 0 )
So, for an update, you need to recreate the table. SQL Server Management Studio makes something like this:
CREATE TABLE dbo.Tmp_Test ( ID int NOT NULL IDENTITY (1, 1), Field1 int NOT NULL, Field2 int NOT NULL ) ON [PRIMARY] GO ALTER TABLE dbo.Tmp_Test ADD CONSTRAINT DF_Test_Field2 DEFAULT 0 FOR Field2 GO SET IDENTITY_INSERT dbo.Tmp_Test ON GO IF EXISTS(SELECT * FROM dbo.Test) EXEC('INSERT INTO dbo.Tmp_Test (ID, Field1, Field2) SELECT ID, Field1, Field2 FROM dbo.Test WITH (HOLDLOCK TABLOCKX)') GO SET IDENTITY_INSERT dbo.Tmp_Test OFF GO DROP TABLE dbo.Test GO EXECUTE sp_rename N'dbo.Tmp_Test', N'Test', 'OBJECT'
And here is the problem. You cannot insert the values into Tmp_Test, because Field2 does not allow NULL values anymore. But it would be possible, if you would turn the NULL-Value into the Default-Value, for example
SELECT ID, Field1, ISNULL( Field2, 0 /* the new default value */ ) FROM dbo.Test
I know, it is easy to workaround this, with a simple Update-Command before Updating
UPDATE Test SET Field1 = 0 WHERE Field1 IS NULL
But perhaps it is worth some consideration to implement this feature.
Thank you,
Michael Kriegner
Comments
I have added your suggestions to an existing Feature Request created regarding the same topic. The reference for this feature request is SC-1428.
Hopefully you will see the requested feature in a future version of SQL Compare.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
For more information visit:
http://www.red-gate.com/MessageBoard/vi ... 1312#51312
David Atkinson
Product Manager
Red Gate Software
Product Manager
Redgate Software