NULLs in unique indexes; Was: Oracle purchases Sleepycat - is this the "other shoe" for MySQL AB?

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? 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.

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...

Here're a few more quotes I stumbled upon while looking for info on this
matter.

from:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp

"Microsoft® SQL Server™ checks for duplicate values when the index is
created (if data already exists) and checks each time data is added with
an INSERT or UPDATE statement. If duplicate key values exist, the CREATE
INDEX statement is canceled and an error message giving the first
duplicate is returned. Multiple NULL values are considered duplicates
when UNIQUE index is created."

from:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls02.htm

"IBM Informix Guide to SQL: Syntax
...
A unique index prevents duplicate values in the customer_num column. A
column with a unique index can have, at most, one NULL value."

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//