Re: unique constraint (%s.%s) violated

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Mon, 27 Jul 2009 00:03:00 -0700
Message-ID: <a2b1e7610907270003m77463e7k3d2b42c987e10d9_at_mail.gmail.com>



Create another temp table without the unique constraint. Try your insert there; Enable the constraint and put exceptions in to exceptions. You will get the rowid of the duplicate record it is trying to insert.
  • kumar

On Sun, Jul 2009 at 5:04 PM, Tony van Lingen < tony_vanlingen_at_technologyonecorp.com> wrote:

> You also get this if there is a trigger on your table which inserts/updates
> a row in a second table that has a unique/primary constraint defined on it:
>
> $ create table atable ( a varchar2(100) not null );
>
> Table created.
>
> $ create table another_table( b number not null primary key ) ;
>
> Table created.
>
> $ create or replace trigger gotcha before insert or update on atable
> 2 begin
> 3 insert into another_table values ( 1 ) ;
> 4 end;
> 5 /
>
> Trigger created.
>
> $ insert into atable values ( 'line 1' );
>
> 1 row created.
>
> $ insert into atable values ( 'line 2' ) ;
> insert into atable values ( 'line 2' )
> *
> ERROR at line 1:
> ORA-00001: unique constraint (TEST.SYS_C0030765) violated
> ORA-06512: at "TEST.GOTCHA", line 2
> ORA-04088: error during execution of trigger 'TEST.GOTCHA'
>
> Obviously in SQL*Plus you see the root cause (trigger failed). i did not
> test if this is also displayed when executed over a dblink..
>
> Cheers,
> Tony
>
> Toon Koppelaars wrote:
>
>>
>> I am not violating any constraint while inserting this row.
>>
>> So you are telling us, it's Oracle's word against yours? :-)
>> I would bet Oracle is right in this case, and that you are inserting
>> duplicate rows and/or a row that already exists for the unique key
>> constraint.
>>
>>
>> On Thu, Jul 23, 2009 at 10:12 PM, Krishan Gupta <krishan.gupta_at_etcc.com<mailto:
>> krishan.gupta_at_etcc.com>> wrote:
>>
>> Hi list,
>>
>>
>> Today while trying to insert a row into a table (have 500000 rows
>> approx) I got the following error :-
>>
>>
>> ERROR at line 1:
>>
>> ORA-00001: unique constraint (%s.%s) violated
>>
>>
>> I am using Oracle Database 10g Enterprise Edition Release
>> 10.2.0.3.0 - 64bit.
>>
>>
>> I am not violating any constraint while inserting this row.
>>
>>
>> Any idea about why I am getting this and How to resolve this error ?
>>
>>
>> Thanks in Advance
>>
>> Krishan
>>
>>
>>
>>
>>
>> --
>> Toon Koppelaars
>> RuleGen BV
>> +31-615907269
>> Toon.Koppelaars_at_RuleGen.com
>> www.RuleGen.com <http://www.rulegen.com/> <http://www.RuleGen.com<http://www.rulegen.com/>
>> >
>> TheHelsinkiDeclaration.blogspot.com<http://thehelsinkideclaration.blogspot.com/><
>> http://TheHelsinkiDeclaration.blogspot.com<http://thehelsinkideclaration.blogspot.com/>>
>>
>>
>> (co)Author: "Applied Mathematics for Database Professionals"
>> www.RuleGen.com/pls/apex/f?p=14265:13<http://www.rulegen.com/pls/apex/f?p=14265:13><
>> http://www.RuleGen.com/pls/apex/f?p=14265:13<http://www.rulegen.com/pls/apex/f?p=14265:13>
>> >
>>
>> --
>
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 27 2009 - 02:03:00 CDT

Original text of this message