RE: composite Unique constraint and null
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <breitliw@xxxxxxxxxxxxx>, <jkstill@xxxxxxxxx>
- Date: Tue, 18 Oct 2005 22:37:15 +0200
very true -- this is where Oracle is not ISO standard compliant, with a UNIQUE
constraint on column combinations.
well, I know DBMSses where this goes wrong as well on single column indexes :-)
it all has to do with how Oracle stores/organizes B*-tree indexes ...
kind regards,
Lex.
------------------------------------------------------------------
Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html
------------------------------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Wolfgang Breitling
Sent: Tuesday, October 18, 2005 21:52
To: jkstill@xxxxxxxxx
Cc: dubey.sandeep@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: composite Unique constraint and null
Interestingly enough, even though one "null" does not equal another "null", i.e.
any comparison with NULL is always false, a unique index on (col1, col2) throws
a unqiue constraint violation when you insert a second (1,null). It appears that
for the index (1,null) IS equal to
(1,null) - or does the unique index use reverse logic and checks if
(1,null) <> (1,null) and, since this is false, concludes that (1,null) must be
equal (1,null) and therefore violate the constraint ;-)
BEGIN:VCARD
VERSION:2.1
N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
TEL;WORK;VOICE:+31.30.2515022
TEL;HOME;VOICE:+31.30.2518795
TEL;CELL;VOICE:+31.62.2955714
TEL;WORK;FAX:+31.30.2523366
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat 10=0D=0AUtrecht 3583
SK=0D=0ANetherlands
URL;WORK:http://www.naturaljoin.nl
EMAIL;PREF;INTERNET:lex.de.haan@xxxxxxxxxxxxxx
REV:20040224T160439Z
END:VCARD
Other related posts:
