Hugo Kornelis (10/28/2014)
One NULL is correct for a single-column constraint, not for composite. A better way to phrase it is that a UNIQUE constraint allows NULLs (as per ANSI standard), but treats NULL as a normal value that also has to conform to the uniqueness constraint (in violation of that same standard).
I wish BOL has clear wording like that. Or even more werbose, kind of
ALTER TABLE t ADD CONSTRAINT cu UNIQUE (a [,...n] ) means that for every row rNew being inserted into t (update is considered as delete + insert) there must hold
NOT EXISTS (SELECT NULL FROM t WHERE (t.a = rNew.a OR COALESCE(t.a, rNew.a) IS NULL) [AND ...n] )