Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated
Date: Thu, 29 Mar 2012 12:17:40 -0700 (PDT)
Message-ID: <1333048660.6627.YahooMailNeo_at_web120701.mail.ne1.yahoo.com>
I believe you are comparing composite UNIQUE index with single column index. Please note that Oracle doesn't store the row if all column values are NULL.
SQL> create table test(id int , pia int, ua int, data date, ver int); Table created.
SQL> create unique index unik on test(data); Index created.
SQL> insert into test values (null, null, null, null, null); 1 row created.
SQL> insert into test values (null, null, null, null, null); 1 row created.
SQL> insert into test values (null, null, null, null, null); 1 row created.
From: jo <jose.soares_at_sferacarta.com> To:
Cc: 'ORACLE-L' <oracle-l_at_freelists.org> Sent: Thursday, 29 March 2012 1:11 PM
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-lReceived on Thu Mar 29 2012 - 14:17:40 CDT