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

From: Alban Hertroys <alban(at)magproductions(dot)nl>
To: Vivek Khera <vivek(at)khera(dot)org>
Cc: Postgresql-General General <pgsql-general(at)postgresql(dot)org>
Subject: Re: NULLs in unique indexes; Was: Oracle purchases Sleepycat
Date: 2006-02-16 16:33:15
Message-ID: 43F4A94B.9000609@magproductions.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(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//

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2006-02-16 16:33:43 Re: Oracle purchases Sleepycat - is this the "other shoe"
Previous Message Robert Treat 2006-02-16 16:29:41 Re: Why does an ON SELECT rule have to be named "_RETURN"?