Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group