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

From: Tony van Lingen <tony_vanlingen_at_technologyonecorp.com>
Date: Mon, 27 Jul 2009 10:04:54 +1000
Message-ID: <4A6CEF26.6030306_at_technologyonecorp.com>

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>
> 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.freelists.org/webpage/oracle-l

Received on Sun Jul 26 2009 - 19:04:54 CDT