RE: ORA-00001: unique constraint (MYDB.SYS_C006557) violated
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Mar 2012 07:27:10 -0400
You're welcome.
Often, midnight or one second past midnight on Jan. 1, 1970 can be well
known as the value for "this is not a real date" in honor of the epoch time
of UNIX. There are other reasonable choices as well, particularly if you are
doing history or something like that. In some cases having a special value
is not useful, but it does prevent row length change when it is updated to
the "real date" that may later be appearing. And you can search for it with
an equals predicate on an index if you're looking for dates that haven't
been processed yet.
If you're not worried about row length change (or if there is a positive
trade-off versus having a lower pctfree) you can use defaults values for
rows inserted without values for particular columns. With a default chosen
that is unlikely to by a real value, you can check the data dictionary for
this value to firewall your application software instead of relying on a
presumed constant (like 1/1/1970.)
Often a little bit of planning of this nature in the data model design
considered against the planned data flow through an application can make
implementing the system easier, and you may indeed end up with a system
containing few nulls. One notable exception is having a final status value
of null to mean "DONE" on an indexed transaction control column, which takes
advantage of the fact that all null index entries do not appear in the index
for routine Oracle indexes (circling back to the original topic).
mwf
-----Original Message-----
From: jo [mailto:jose.soares@xxxxxxxxxxxxxx]
Sent: Friday, March 30, 2012 2:21 AM
To: mwf@xxxxxxxx
Cc: 'ORACLE-L'
Subject: Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated
Ok, now I understand.
Thanks for your explanation, Mark,
and thanks to everyone to answer my question.
j
ps:
You said you prefer to avoid null values to be inserted into your db.
While it is easy to apply to numeric or text columns, I wonder what do you
insert into a date column instead of a null.
Mark W. Farnham wrote:
> (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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> 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@xxxxxxxxxxxxx
>> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
>> 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
>>
>>
>>
>>
>>
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>> --
>> //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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
