RE: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 29 Mar 2012 14:28:47 -0400
Message-ID: <04ee01cd0dd9$c8d50380$5a7f0a80$_at_rsiz.com>


(1,1,1,null,1) might be equal to (1,1,1,null,1), so the composite value cannot be asserted to be unique.

(null) versus (null) in a single column index is I take it considered to be a missing value for convenience.

Generalizing, (null,null,null,null,null) for a 5 column composite as in your example can also be duplicated.

So the consistency is that if the index key's complete value is null, they don't guard against those as duplicate values.

If at least one column value in an index is not null, then a duplicate will not be allowed.

If a unique index/constraint is completely null, Oracle won't declare it to be a uniqueness violation if you toss in another completely null tuple.

If a unique index/constraint has at least something filled in then Oracle won't let a possible duplicate into the table.

Since a single column index has just the one column, you could take that to mean Oracle's treatment is different for that case, but you'd be wrong.

It just happens that the whole entry is null by virtue(?) of that one column being null.

Those were the null value/logic choices made a long time ago for Oracle. I'm not sure the folks who discussed the reasons underlying this choice are even still alive, so take my presumption that it was because it seemed logical to presume completely null was a missing value. It may just have been easier to implement that way. From a use perspective it is convenient in some cases, although I personally prefer avoiding null values being inserted into my databases and I look askance at data models that require what should be a unique composite from allowing nulls in the first place.

So no, there is not a difference in the use of NULLs between individual indexes and composite indexes.

regards,

mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of jo
Sent: Thursday, March 29, 2012 1:11 PM
Cc: 'ORACLE-L'
Subject: Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

I think I didn't understand the answer, sorry, my english is not so good. My question is:

There is a difference in the use of NULLs between individual indexes and composite indexes?

j

Mark W. Farnham wrote:
> You can assert NEITHER that two nulls are equal nor unequal. Oracle
> has that correct.
>
> Are you used to using promoting some database that pretends it CAN
> assert inequality between nulls?
>
> With this question following hot on the heels of the well understood
> trade-off of Oracle for speed and caching rather than guaranteed
> ordered no-skip use for sequences (for which the old solution of
> incrementing a column in a table is as acid as the database), I'm
> becoming really curious about whether you're legitimately asking
> question or intentionally create confusion.
>
> Oracle's treatment and explanation of the trade-off for sequences
> dates to at least November 1988.
> Oracle's treatment and explanation of its logic values for nulls and
> uniqueness of indexes is older than that.
>
> If you're really asking questions and this was just a coincidence,
> that is quite a coincidence indeed.
>
> If you ARE trying to learn the difference between some other RDBMS
> system and Oracle I suggest you search for a guide with the title
> something like "Oracle for DB2 DBAs" or similar titles, as well as
> reading the Oracle Concepts Guide cover to cover as a start.
>
> Based on the first two questions I've seen from you, that will save
> you a ton of time if your interest is legitimate.
>
> Yet please do not feel unwelcome on this list. That is not my intent.
>
> Regards,
>
> mwf
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of jose soares
> Sent: Thursday, March 29, 2012 10:40 AM
> To: ORACLE-L
> Subject: ORA-00001: unique constraint (MYDB.SYS_C006557) violated
>
> Hi all,
>
> I think I have a problem with NULL values on composit indexes:
>
> create table test(id int primary key, pia int, ua int, data date, ver
> int); create unique index unik on test(pia int, ua , data , ver )
> insert into test values (1,37,76,null,1); insert into test values
> (2,37,76,null,1);
>
> ORA-00001: unique constraint (MYDB.SYS_C006557) violated
>
> I hope Oracle knows that no two null values are equal :-)
>
> ----------------
>
> this one works, instead:
>
> create table test(id int primary key, pia int, ua int, data date, ver
> int); create unique index unik on test(data ) insert into test values
> (1,37,76,null,1); insert into test values (2,37,76,null,1);
>
>
> what's wrong?
>
> j
>
>
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Jose Soares                              _/_/  
Sferacarta Net                                  
Via Bazzanese 69                       _/_/     _/_/_/
40033 Casalecchio di Reno             _/_/   _/_/  _/_/
Bologna - Italy                      _/_/   _/_/  _/_/
Ph  +39051591054              _/_/  _/_/   _/_/  _/_/
fax +390516131537            _/_/  _/_/   _/_/  _/_/
web:www.sferacarta.com        _/_/_/       _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file
allegato sono riservate e, comunque, destinate esclusivamente alla persona o
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196.
La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza,
l’integritÓ e la sicurezza della presente mail non possono essere garantite.
Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to
legislative decree 30 June 2003, n. 196. It may contain confidential or
privileged information. You should not copy or use it to disclose its
contents to any other person. Transmission cannot be guaranteed to be
error-free, complete and secure. If you are not the intended recipient and
receive this communication unintentionally, please inform us immediately and
then delete this message from your system. Thank you.

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 29 2012 - 13:28:47 CDT

Original text of this message