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 ...
-- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Wolfgang Breitling Sent: Tuesday, October 18, 2005 21:52 To: jkstill@(protected) Cc: dubey.sandeep@(protected); oracle-l@(protected) 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 ;-)
