Re: How to enforce uniqueness when NULL values are present?

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Sun, Mar 11, 2007 at 11:09:56AM +0100, Christian Schr=F6der wrote:
>> Of course, if a NULL always means "unknown", then this approach doesn't
>> make sense. Where can I find an authorative definition of what NULL
>> means?

> Nowhere.

Well, in this context the authoritative definition is easy to find:
it's where the SQL spec says that two rows containing NULLs don't
violate a unique constraint. SQL92 section 11.7 defines unique
constraints as requiring success of a <unique predicate>, and
section 8.9 defines <unique predicate> thusly:

2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.

regards, tom lane