This discussion is archived
6 Replies Latest reply: Aug 23, 2013 4:03 PM by tmazight RSS

Oracle PK Violated with Sequence

tmazight Newbie
Currently Being Moderated

Hello,

 

I really have serious problem

 

I'm making an insert on a table wich have a primary key contraint using a sequence like this :

 

  select EXPLOIT.SEQ_ID_SUPPR_LICENCE.NEXTVAL

  into SeqIdLicence

  from dual;

 

  insert into EXPLOIT.SUPPR_LIC_INFO_LICENCE

          ( ID_SUPPR_LICENCE,

          .............

          ............

select      SeqIdLicence,

          ............

          ............

 

ORA-00001: violation de contrainte unique (EXPLOIT.PK_SUPPR_LIC_INFO_LICENCE)

 

The primary key is  ID_SUPPR_LICENCE.

 

The Value of the NEXTVAL est 59015 at the moment and the max(ID_SUPPR_LICENCE) is 58996.

 

Can anyone help me resolve this!

 

Regards.

  • 1. Re: Oracle PK Violated with Sequence
    Justin Cave Oracle ACE
    Currently Being Moderated

    What you're saying doesn't make sense.

     

    If ID_SUPPR_LICENSE is the primary key and the NEXTVAL of the sequence is returning a value that is larger than any value stored in the table, you would not get a constraint violation error.  Either IS_SUPPR_LICENSE is not the primary key or NEXTVAL is not returning the value you say it is or there is actually a duplicate row in the table.

     

    If you are confident that your statements are all accurate, then you've encountered a bug that you need to report to Oracle Support and get a patchset for.  Of course, it is wildly more likely that you have made a mistake than that Oracle has introduced a fatal bug in a very basic piece of functionality that was able to go uncaught through all the testing Oracle does and through all the systems that others build

     

    Justin

  • 2. Re: Oracle PK Violated with Sequence
    tmazight Newbie
    Currently Being Moderated

    I agree that it does not make sens.

     

    now i now that the problem doesn't come from the sequence. I inserted a random values of 295468256 in the primary key and still have the same error.

     

    Select max(id_suppr_licence) from TheTable five me value of 58015 , the same as an ORDER BY id_suppr_licence DESC.

     

    Thx.

  • 3. Re: Oracle PK Violated with Sequence
    kendenny Expert
    Currently Being Moderated

    Are you certain that id_suppr_licence is the primary key for that table? Looks to me like it's not.

  • 4. Re: Oracle PK Violated with Sequence
    mtefft Journeyer
    Currently Being Moderated

    There are a few possiblle explanations for this.

    First of all, please post the DDL for the sequence.

     

    Next, you say: The Value of the NEXTVAL est 59015 at the moment. How do you know that? Are you looking in the data dictionary? Do you understand that sequences can be cached? Do you understand the implications of that cacheing in a RAC environment?

     

    I suspect that your program is not getting 59015 when it pulls a value from the sequence.

  • 5. Re: Oracle PK Violated with Sequence
    Christine Schnittker Explorer
    Currently Being Moderated

    How many rows does the select in your insert-select return? You're reusing the same sequence number for all of them. I suggest to change the insert to

      insert into EXPLOIT.SUPPR_LIC_INFO_LICENCE

              ( ID_SUPPR_LICENCE,

              .............

              ............

      select  EXPLOIT.SEQ_ID_SUPPR_LICENCE.NEXTVAL,

              ............

              ............

    //Tine

  • 6. Re: Oracle PK Violated with Sequence
    tmazight Newbie
    Currently Being Moderated

    Thx All and Special Thank Goes to Christine

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points