1 2 Previous Next 20 Replies Latest reply: Dec 8, 2009 9:04 AM by 730428 RSS

    How to avoid the error - "ORA-00001: unique constraint violated"

    R. Royal
      I've this tables (with more 1000 records):

      CREATE TABLE MY_TAB
      (
        PK_ID           VARCHAR2(32),
        COD_ID           VARCHAR2(32),
        NAME_FIRST       VARCHAR2(32),
        NAME_LAST        VARCHAR2(32),
        EMAIL            VARCHAR2(32),
        CITY           VARCHAR2(32),
        FLAG             CHAR(1)
      );
      
      ALTER TABLE MY_TAB
      ADD (CONSTRAINT PK_PK_ID PRIMARY KEY (PK_ID));
      The primary key PK_ID is linked in this way:
      name_last||'_'||substr(name_first,1,3)||substr(cod_id,-2)

      PK_ID................COD_ID.........NAME_FIRST.........NAME_LAST.........EMAIL........CITY.......FLAG
      LUISS_SAM99...009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........1
      SIMPSON_TOM99...009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........1
      KAYNE_JOH99...009656........JOHN..............KAYNE.............YYYY@bb.com..........PARIS........1

      I'd like a stored procedure with in INPUT COD_ID that insert a new record with same values of the previous record BUT with PK_ID||'_'||progressive (or other value) and put FLAG='0'

      For example:
      execute INS_DUP_COD ('009999');
      
      PL/SQL procedure successfully completed
      My Output Will be:

      PK_ID................COD_ID.........NAME_FIRST.........NAME_LAST.........EMAIL........CITY.......FLAG
      LUISS_SAM99...009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........1
      LUISS_SAM99_1...009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........0
      SIMPSON_TOM99...009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........1
      KAYNE_JOH99...009656........JOHN..............KAYNE.............YYYY@bb.com..........PARIS........1
      execute INS_DUP_COD ('009999');
      
      PL/SQL procedure successfully completed
      My Output Will be:

      PK_ID................COD_ID.........NAME_FIRST.........NAME_LAST.........EMAIL........CITY.......FLAG
      LUISS_SAM99...009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........1
      LUISS_SAM99_1...009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........0
      LUISS_SAM99_2...009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........0
      SIMPSON_TOM99...009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........1
      KAYNE_JOH99...009656........JOHN..............KAYNE.............YYYY@bb.com..........PARIS........1
      execute INS_DUP_COD ('009345');
      
      PL/SQL procedure successfully completed
      My Output Will be:

      PK_ID................COD_ID.........NAME_FIRST.........NAME_LAST.........EMAIL........CITY.......FLAG
      LUISS_SAM99...009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........1
      LUISS_SAM99_1...009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........0
      LUISS_SAM99_2...009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........0
      SIMPSON_TOM99...009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........1
      SIMPSON_TOM99_1...009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........0
      KAYNE_JOH99...009656........JOHN..............KAYNE.............YYYY@bb.com..........PARIS........1

      How Can I write my stored procedure to avoid "ORA-00001: unique constraint violated" on PK_ID column?

      Thanks in advance!
        • 1. Re: How to avoid the error - "ORA-00001: unique constraint violated"
          Arun Kumar Gupta
          Have you tried writing a procedure?

          In words take the input parameter and check if one exists in the code id column of the table. If no insert a new record and if yes use teh substr function on PK_ID column to get the last sequence used and then increment it by one to insert a new record.


          Regards
          Arun
          • 2. Re: How to avoid the error - "ORA-00001: unique constraint violated"
            Hoek
            Without knowing how your procedure looks like:

            1) change the PK to an Oracle sequence no.

            2) if you must have name_last||'_'||substr(name_first,1,3)||substr(cod_id,-2) as a PK, then determine the progressive (or other value) by using analytics.

            name_last||'_'||substr(name_first,1,3)||substr(cod_id,-2)

            You probably could use something like:
            select name_last||'_'||substr(name_first,1,3)||substr(cod_id,-2)
                   ||'_'||
                   row_number() over ( partition by name_last||'_'||substr(name_first,1,3)||substr(cod_id,-2) 
                                           order by name_last||'_'||substr(name_first,1,3)||substr(cod_id,-2)  
                                      )
            from   your_table
            • 3. Re: How to avoid the error - "ORA-00001: unique constraint violated"
              Peter Gjelstrup
              Raf Royal,

              Weren't you warned many times about that design and encouraged to use a sequence, instead?

              How to put the my scripts in a STORED PROCEDURE

              @Arun, easy said. But not easy done in a multi user environment.

              Regards
              Peter
              • 4. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                730428
                create or replace procedure INS_DUP_COD (p_id in varchar2) is
                begin
                  insert into my_tab 
                  select pk_id, cod_id, name_first, name_last, email, city, flag from (
                     select substr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),1,instr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),'_',1,2))||
                              (to_number(substr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),instr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),'_',1,2)+1))+1) pk_id, 
                              cod_id, name_first, name_last, email, city, '0' flag, 
                          rank() over (order by pk_id desc) rn
                    from my_tab 
                   where cod_id=p_id)
                   where rn=1; 
                end;
                /
                Max
                • 5. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                  R. Royal
                  Massimo,
                  I tried your stored procedure but I get this output:
                  execute INS_DUP_COD ('009999');
                  
                  PL/SQL procedure successfully completed
                  PK_ID...................COD_ID.........NAME_FIRST.........NAME_LAST.........EMAIL........CITY.......FLAG
                  LUISS_SAM99.........009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........1
                  LUISS_SAM99_1000...009999........SAMMY..............LUISS.............ddd@bb.com..........ROME........0
                  SIMPSON_TOM99.....009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........1
                  KAYNE_JOH99.......009656........JOHN..............KAYNE.............YYYY@bb.com..........PARIS........1

                  execute INS_DUP_COD ('009999');
                  
                  ORA-00001: unique constraint violated
                  Have someone any idea??
                  • 6. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                    Hoek
                    Have you tried using row_number() already?
                    • 7. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                      730428
                      It works for me:
                      SVIL>create or replace procedure INS_DUP_COD (p_id in varchar2) is
                        2  begin
                        3    insert into my_tab 
                        4    select pk_id, cod_id, name_first, name_last, email, city, flag from (
                        5       select substr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),1,instr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),'_',1,2))||
                        6                (to_number(substr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),instr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),'_',1,2)+1))+1) pk_id, 
                        7                cod_id, name_first, name_last, email, city, '0' flag, 
                        8            rank() over (order by pk_id desc) rn
                        9      from my_tab 
                       10     where cod_id=p_id)
                       11     where rn=1; 
                       12  end;
                       13  / 
                      
                      Procedura creata.
                      
                      SVIL>
                      SVIL>insert into my_tab (pk_id, cod_id) values ('LUISS_SAM99','009999');
                      
                      Creata 1 riga.
                      
                      SVIL>select pk_id from my_tab;
                      
                      PK_ID
                      --------------------
                      LUISS_SAM99
                      
                      SVIL>exec INS_DUP_COD('009999')
                      
                      Procedura PL/SQL completata correttamente.
                      
                      SVIL>select pk_id from my_tab;
                      
                      PK_ID
                      --------------------
                      LUISS_SAM99
                      LUISS_SAM99_1
                      
                      
                      SVIL>exec INS_DUP_COD('009999')
                      
                      Procedura PL/SQL completata correttamente.
                      
                      SVIL>select pk_id from my_tab;
                      
                      PK_ID
                      --------------------
                      LUISS_SAM99
                      LUISS_SAM99_1
                      LUISS_SAM99_2
                      Max
                      • 8. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                        BluShadow
                        Peter Gjelstrup wrote:
                        Raf Royal,

                        Weren't you warned many times about that design and encouraged to use a sequence, instead?

                        How to put the my scripts in a STORED PROCEDURE
                        Yep, I warned him, but would he listen? ?:|

                        {noformat}*sigh*{noformat}
                        • 9. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                          Peter Gjelstrup
                          Massimo Ruocchio wrote:
                          It works for me:
                          Not for me (*Or should I say us*)
                          USERA> exec INS_DUP_COD('009999')
                          PL/SQL procedure successfully completed.
                          USERA> exec INS_DUP_COD('009999')
                          PL/SQL procedure successfully completed.
                          USERA> select pk_id, cod_id from my_tab
                          
                          PK_ID                            COD_ID                          
                          -------------------------------- --------------------------------
                          LUISS_SAM99                      009999                          
                          LUISS_SAM99_1                    009999                          
                          LUISS_SAM99_2                    009999                          
                          
                          3 rows selected.
                          
                          -- Do not commit, yet.
                          
                          
                          USERB>exec INS_DUP_COD('009999')
                          -- Still waiting?
                          
                          
                          
                          USERA>COMMIT;
                          
                          -- USER B now has:
                          BEGIN INS_DUP_COD('009999'); END;
                          
                          *
                          ERROR at line 1:
                          ORA-00001: unique constraint (USER.PK_PK_ID) violated
                          ORA-06512: at "USER.INS_DUP_COD", line 3
                          ORA-06512: at line 1
                          
                          
                          USERB>
                          Why are so many eager to help on something that is obviously wrong?

                          Regards
                          Peter
                          • 10. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                            R. Royal
                            I tried (with commit) and seems working correctly:
                            create or replace procedure INS_DUP_COD (p_id in varchar2) is
                                begin
                                  insert into my_tab
                                  select pk_id, cod_id, name_first, name_last, email, city, flag from (
                                     select substr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),1,instr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),'_',1,2))||
                                              (to_number(substr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),instr(decode(instr(pk_id,'_',1,2),0, pk_id||'_0',pk_id),'_',1,2)+1))+1) pk_id,
                                              cod_id, name_first, name_last, email, city, '0' flag,
                                          rank() over (order by pk_id desc) rn
                                    from my_tab
                                  where cod_id=p_id)
                                  where rn=1;
                                  COMMIT;
                               end;
                               /
                            I know it's much better to use a sequence BUT my business roles requires me to use name_last and name_first into primary key.
                            • 11. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                              730428
                              Peter it's absolutely correct and appreciable that you suggest a better solution but I think the OP has the right to follow his own ideas and, if I can, I help him.
                              I'm a proud atheist, but I know priests call this "free will" ;)

                              Max
                              • 12. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                                730428
                                Raf, the COMMIT doesn't change anything if you're not running multiple sessions at the same time.

                                Max

                                PS Please mark the question as answered so the filter can do its job!
                                • 13. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                                  BluShadow
                                  Raf Royal wrote:
                                  I know it's much better to use a sequence BUT my business roles requires me to use name_last and name_first into primary key.
                                  As I said on the other thread...

                                  The business should not be dictating how something is technically implemented.
                                  Technically you should use a sequence as your internal primary key.
                                  Business wise you can have a user facing key for the records as you require.

                                  Technical requirements should be defined by technical experts, not the business users/managers.
                                  • 14. Re: How to avoid the error - "ORA-00001: unique constraint violated"
                                    Hoek
                                    Decides to wear his "Just say NO!" t-shirt next meeting ;)
                                    1 2 Previous Next