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

Vivek Khera wrote:
>
> On Feb 16, 2006, at 6:27 AM, 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...
>
>
> Ok, fair enough... but you still get different behavior depending on
> your table type in mysql, which is just idiotic...  At least with  every
> other system, you get what you get all the time, not just some  of the
> time.

I wasn't trying to justify mysql's behaviour in this respect (Me? No way!)

It merely got me wondering what the correct implementation of a UNIQUE
constraint regarding NULL values would be. Verifying my own ideas
against the PostgreSQL docs and various results from Google turned up
some interesting differences in points of view. Hence the quotes :P

I still think one shouldn't allow NULL values in unique constraints
unless it's the _only_ value (any value is unique if there are no other
values to compare with, after all), but I can't compare myself to
C.Date, Darwen or Tom Lane... I'm not a database deity :P

I suspect they have some pretty good reasons to treat NULL values in a
UNIQUE constraint as different even from other NULL values. It sure
makes me curious though ;)

Regards,

--
Alban Hertroys
alban@magproductions.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//