NULL in check constraints
July 29, 2009
Posted by on
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”