I created a unique constraint on a column called Col1 in table Test1 to enforce uniqueness with the exception of NULL. The entry for "Unique Constraint" in SQL Server Books Online (BOL) specifically says that you can create a unique constraint on columns that allow NULL. However, as soon as I add data and try to enter a second record with NULL in Col1, I receive a message that says I've violated the unique constraint. As I understand unique constraints (as opposed to unique indexes), they allow duplicate NULLS. Am I correct?

BOL can be misleading on this topic. The correct answer is no: The unique constraint doesn't let you add multiple records that contain a value of NULL.

Here's why I believe the reference in BOL is misleading. In the "Unique Constraint" entry, BOL says, "UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values." In addition, the ANSI SQL-92 standard says that NULL isn't equal to NULL—in other words, the expression (1=1) evaluates to true, whereas (NULL = NULL) evaluates to false—so it seems reasonable to assume that the unique constraint would allow multiple values of NULL. However, keep in mind that you use a standard unique index to physically enforce a unique constraint. The "Create Index" section of BOL says, "Multiple NULL values are considered duplicates when a UNIQUE index is created."

In general, a value of NULL isn't equal to NULL, but two different NULL values are treated as equal for the purpose of enforcing a unique index. Alternatively, you could easily use a set of UPDATE and INSERT triggers to create an index that would allow just one NULL value in the set of composite keys for the index.

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Not being a fan of triggers I went looking for other solutions to this problem. As mentioned elsewhere on this site by the MVPs, you can create a view with schemabinding that selects the columns that define your uniqueness constraint and return only the non NULL values in the view. Then create a unique clustered index on the view. (works in Standard Edition SQL Server too). If you have more than one ANSI SQL-92 compliant uniqueness constraint to implement you can create multiple views, each with their own unique clustered index. Alternatively consider creating a computed column that substitutes another unique value in place of a NULL, then place a non clustered index on the computed column(s). For example, to ensure uniqueness on Col2 of type int where Col1 is a primary key of type int you could create the following : create table Tab1 ( Col1 int not null identity primary key, Col2 int null, Col3 as coalesce(Col2, Col1 + 1000000000) UNIQUE )

Peter Ellis- September 21, 2004

Article Rating 5 out of 5

Good explanation and very useful

Anonymous User- January 14, 2005

Article Rating 5 out of 5

Thanks for the tip about "a unique clustered index on the view". Wonderful!

Anonymous User- March 06, 2005

Article Rating 5 out of 5

 
 

ADS BY GOOGLE