| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: composite Unique constraint and null
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.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On
Behalf Of Wolfgang Breitling
Sent: Tuesday, October 18, 2005 21:52
To: jkstill_at_gmail.com
Cc: dubey.sandeep_at_gmail.com; oracle-l_at_freelists.org
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 ;-)
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 18 2005 - 15:39:29 CDT
 |
 |