Unique Constraint column can only contain one NULL value

Question

A Unique Constraint can be created upon a column that can contain NULLs. However, at most, only a single row may ever contain a NULL in that column.

I do not understand why this is the case since, by definition, a NULL is not equal to another NULL (since NULL is really an unknown value and one unknown value does not equal another unknown value).

My questions: 1. Why is this so? 2. Is this specific to MsSQL?

I have a hunch that it is because a Unique Constraint can act as a reference field for a Foreign Key and that the FK would otherwise not know which record in the reference table to which it was refering if more than one record with NULL existed. But, it is just a hunch.

(Yes, I understand that UCs can be across multiple columns, but that doesn't change the question; rather it just complicates it a bit.)

Answer 1

Yes, it's "specific" to Microsoft SQL Server (in that some other database systems have the opposite approach, the one you expected - and the one defined in the ANSI standard, but I believe there are other database systems that are the same as SQL Server).

If you're working on a version of SQL Server that supports filtered indexes, you can apply one of those:

CREATE UNIQUE INDEX IX_T ON [Table] ([Column]) WHERE [Column] IS NOT NULL

(But note that this index cannot be the target of an FK constraint)

---

The "Why" of it really just comes down to, that's how it was implemented long ago (possibly pre-standards) and it's one of those awkward situations where to change it now could potentially break a lot of existing systems.

Re: Foreign Keys - you would be correct, if it wasn't for the fact that a NULL value in a foreign key column causes the foreign key not to be checked - there's no way (in SQL Server) to use NULL as an actual key.

Answer 2

Yes it's a SQL Server feature (and a feature of a few other DBMSs) that is contrary to the ISO SQL standard. It perhaps doesn't make much sense given the logic applied to nulls in other places in SQL - but then the ISO SQL Standard isn't very consistent about its treatment of nulls either. The behaviour of nullable uniqueness constraints in Standard SQL is not very helpful. Such constraints aren't necessarily "unique" at all because they permit duplicate rows. E.g., the constraint UNIQUE(foo,bar) permits the following rows to exist simultaneously in a table:

foo    bar
------ ------
999    NULL
999    NULL

(!)

Avoid nullable uniqueness constraints. It's usually straightforward to move the columns to a new table as non-nullable columns and put the uniqueness constraint there. The information that would have been represented by populating those columns with nulls can (presumably) be represented by simply not populating those columns in the new table at all.