Blog ala Vidar

SQL, AppFrame and other cool technologies

NULL in check constraints

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

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

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:

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

Comments are closed.

%d bloggers like this: