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

Martijn van Oosterhout <kleptog@svana.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