Re: NULLs in unique indexes; Was: Oracle purchases Sleepycat

Alban Hertroys wrote:
> Vivek Khera wrote:
>> http://dev.mysql.com/doc/refman/5.1/en/bdb-restrictions.html
>>
>> I especially like the third restriction.  How on earth do people live
>> with this software?
>
> That's the part where they allow only one NULL value in a unique index,
> right? Opinions seem to differ on this matter...
>
> Is it possible to guarantee that an index is unique at all if it
> contains NULL values?

No.

 > If I have an index containing [1,2,3,NULL,4,5],
> can I say that NULL (it being an "unknown" value) does not equal one of
> the other values? Or for that matter, if I'd have multiple NULL values,
> can I say they aren't equal? I think not.

Exactly so.

> The docs say
> (http://www.postgresql.org/docs/8.1/static/indexes-unique.html):
> "When an index is declared unique, multiple table rows with equal
> indexed values will not be allowed. Null values are not considered equal."
>
> But according to:
> http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/21064
>
> "The definition of unique constraints in the SQL standards specifies
> that the column definition shall not allow null values.", although that
> doesn't literally mean NULL values in unique indexes are not allowed...

It's a tricky question. The only really clean solution is to say that a
UNIQUE constraint requires NOT NULL on all its columns. This is what
happens when you define a primary key of course.

I suppose you *could* say that with a unique constraint over (a,b,c)
then if (1,2,null) is already in the table (1,2,<anything>) is then
forbidden since you can't guarantee it won't conflict. In effect saying
"can I prove this is different from existing values", which of course is
  "no" if you're comparing against nulls.

If you're only allowing one null value, you're saying NULL=NULL which of
course is not true. I can see *why* dbms builders choose to do that, but
I don't think it's right.

--
   Richard Huxton
   Archonet Ltd