Unique constraint vs NULL values in SQL Server
Lately, I’ve been working with a SQL Server database table that contains people. Each person has a system-generated ID as their primary identifier in the system, but we also hold a national identifier (something like a National Insurance or Social Security Number) in a second column.
Today, I’ve been changing the application to allow the entry of new people without a national identifier as we need to cater for rare cases where it is not known or doesn’t exist. The national identifier column had a unique constraint and disallowed NULL values.
First order of the day was to remove the NOT NULL from the column. That done, I pretty much expected it to work, after all NULL is a concept, not a real value, right? So if you put a unique constraint on a column that allows NULL values, it will be enforced for rows that have an actual value in that column but you could have as many NULLs as you like, surely?
Apparently not. At least not in SQL Server. According to the official documentation:
“…as with any value participating in a UNIQUE constraint, only one null value is allowed per column.”
Hmm, not what I was expecting, and according to some sources (though I couldn’t actually find the spec to confirm) not what the ANSI SQL specification expects either.
Fortunately, there was a helpful comment on the same page from community member pinchy showing how, in SQL Server 2008 at least, this can be worked around using the new filtered index feature, eg:
CREATE UNIQUE NONCLUSTERED INDEX idx_nationalId_notNull ON person(nationalId) WHERE nationalId IS NOT NULL
Fortunately this application is on SQL Server 2008, so this worked for me. I don’t like the look of the hoops you need to jump through to achieve this in SQL Server 2005.