Blog ala Vidar

I just stumbled over a bug in both SQL Server Engine and management studio thanks to Håvard Ravatn.

CREATE TABLE [dbo].[Table_1](
	[field1] [nvarchar](50) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Table_1] WITH CHECK ADD CONSTRAINT [CK_Table_1] CHECK (([field1]=NULL))
ALTER TABLE [dbo].[Table_1] CHECK CONSTRAINT [CK_Table_1]

INSERT INTO Table_1 (field1) VALUES ('b')

This will actually allow you to insert ‘b’ into field1, although the check constraint says field1 = NULL. Now you’re probably thinking “has Vidar gone mad? He should know that you can’t do field1 = NULL, you do field1 IS NULL”. Well, don’t worry just yet. The reason I found this was because of a bug in management studio. If you create a check constraint for field1 where the expression is:

Field1 IN (NULL, ‘a’, ‘b’)

and then save the table, the constraint is actually stored as:

Field1 = NULL OR Field1 = ‘a’ OR Field1 = ‘b’

So, be aware of this bug.

Also, remember that NULL != NULL. One NULL value is NEVER the same as another NULL value, unless ANSI_NULLS are turned off.

I’ve reported this bug to Microsoft. Please validate it, as Microsoft fixes all bugs that has more than 5 validations: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=478176

One thing I forgot to mention, as Johnny comments is that this is NOT the way to do things. Use “Allow null”