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
