How can we help you today? How can we help you today?

FROM NULL TO NOT NULL, BUT WITH DEFAULT

I have a suggestion for a new option. Sometimes it happens, that we create a table like this:
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
informatika
0

Comments

3 comments

Sort by
  • Eddie D
    Thank for your post into the forum.

    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
    Eddie D
    0
  • David Atkinson
    SQL Compare and SQL Source Control can now work side by side to achieve what you describe. You can commit your own custom script to source control (in this case you would add the UPDATE statement) and SQL Compare would be able to reuse this script in future deployments.

    For more information visit:

    http://www.red-gate.com/MessageBoard/vi ... 1312#51312

    David Atkinson
    Product Manager
    Red Gate Software
    David Atkinson
    0
  • jamessmith

    Interestingly, I’ve seen similar discussions about handling constraints and data changes while writing about different systems and updates in gaming communities as well. For example, when explaining new features and changes for custom servers like Null’s Brawl (especially the Pierce & Glowbert update), we also emphasize how certain settings or defaults affect the overall behavior of the system. I covered some details about that update here!

    It’s always interesting how both database design and game server configurations rely on clear defaults and constraints to avoid unexpected behavior.

    jamessmith
    -1

Add comment

Please sign in to leave a comment.