Unique Constraint Column Can Contain Only One NULL Value

Question

A unique constant can be created in a column that can contain NULL. However, at most, only one row can ever contain NULL in this column.

I don’t understand why this is so, since by definition NULL is not equal to another NULL (since NULL is really an unknown value, and one unknown value is not equal to another unknown value).

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

I have a suspicion that this is due to the fact that the Unique constraint may act as a reference field for a foreign key and that FK otherwise does not know which record in the reference table to which it referred if more than one record with NULL existed. But this is just a hunch.

(Yes, I understand that UC can be across multiple columns, but this does not change the question, but rather complicates it.)

Answer 1

Yes, it is "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 that other database systems exist which are the same as SQL Server).

If you are working on a version of SQL Server that supports filtered indexes, you can apply one of the following:

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

(But keep in mind that this index cannot be the target of FK restrictions)

---

The “why” really just comes down to the fact that it was implemented a long time ago (perhaps preliminary standards), and one of such awkward situations where it can be changed can now potentially violate many existing systems.

Re: Foreign Keys - you would be right if it were not for the fact that the NULL value in the foreign key column forces you not to check the foreign key - there is no way (in SQL Server) to use NULL as the actual key.

Answer 2

Yes, this is a function of SQL Server (and a function of several other DBMSs), which contradicts the ISO SQL standard. This may not make much sense given the logic applied to zeros elsewhere in SQL, but then the ISO SQL standard is not very consistent with its handling of zeros. The behavior of limited uniqueness constraints in standard SQL is not very useful. Such restrictions are not necessarily "unique" at all, since they allow duplication of strings. For example, the UNIQUE(foo,bar) allows the following rows to exist simultaneously in a table:

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

(!)

Avoid restrictions on uniqueness. It is usually simple to move columns to a new table as columns with a non-empty value and place a unique constraint there. Information that would be represented by filling these columns with zeros can (presumably) be presented simply by not filling these columns in a new table at all.