Why does a UNIQUE constraint allow only one NULL?

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL.

It wasn't a very smart decision. What they should have done is have the default behavior adhere to the ANSI standard, and if they really wanted this peculiar behavior, allow it through a DDL option like WITH CONSIDER_NULLS_EQUAL or WITH ALLOW_ONLY_ONE_NULL.

Of course, hindsight is 20/20.

And we have a workaround, now, anyway, even if it isn't the cleanest or most intuitive.

You can get the proper ANSI behavior in SQL Server 2008 and above by creating a unique, filtered index.

CREATE UNIQUE INDEX foo ON dbo.bar(key) WHERE key IS NOT NULL;

This allows more than one NULL value because those rows are completely left out of the duplicate checking. As an added bonus, this would end up being a smaller index than one that consisted of the entire table if multiple NULLs were allowed (especially when it isn't the only column in the index, it has INCLUDE columns, etc). However, you may want to be aware of some of the other limitations of filtered indexes:

• How filtered indexes could be a more powerful feature

Correct. The implementation of a unique constraint or index in sql server allows one and only one NULL. Also correct that this technically doesn't fit with the definition of NULL but it's one of those things they did to make it more useful even though it isn't "technically" correct. Note a PRIMARY KEY (also a unique index) does not allow NULLs (of course).

First - stop using the phrase "Null value", it will just lead you astray. Instead, use the phrase "null marker" - a marker in a column indicating that the actual value in this column is either missing or inapplicable (but note that the marker does not say which of those options is actually the case¹).

Now, imagine the following (where the database does not have complete knowledge of the modeled situation).

Situation          Database

ID   Code          ID   Code
--   -----         --   -----
1    A             1    A
2    B             2    (null)
3    C             3    C
4    B             4    (null)

The integrity rule we are modelling is "the Code must be unique". The real-world situation violates this, so the database shouldn't allow both items 2 and 4 to be in the table at the same time.

The safest, and least-flexible, approach would be to disallow null markers in the Code field, so there is no possibility of inconsistent data. The most flexible approach would be to allow multiple null markers and worry about uniqueness when values are entered.

The Sybase programmers went with the somewhat-safe, not-very-flexible approach of only allowing one null marker in the table - something commentators have been complaining about ever since. Microsoft have continued this behaviour, I guess for backwards compatibility.

---

¹ I am sure I read somewhere that Codd considered implementing two null markers - one for unknown, one for inapplicable - but rejected it, but I can't find the reference. Am I remembering correctly?

P.S. My favourite quote about null: Louis Davidson, "Professional SQL Server 2000 Database Design", Wrox Press, 2001, page 52. "Boiled down to a single sentence: NULL is evil."