6 Replies Latest reply on Aug 23, 2013 11:03 PM by tmazight

    Oracle PK Violated with Sequence




      I really have serious problem


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



        into SeqIdLicence

        from dual;



                ( 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!



        • 1. Re: Oracle PK Violated with Sequence

          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



          • 2. Re: Oracle PK Violated with Sequence

            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.



            • 3. Re: Oracle PK Violated with Sequence

              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

                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

                  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,




                  • 6. Re: Oracle PK Violated with Sequence

                    Thx All and Special Thank Goes to Christine