This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Dec 8, 2009 7:04 AM by 730428 RSS

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

R. Royal Newbie
Currently Being Moderated
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"
    ArunKumarGupta Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Have you tried using row_number() already?
  • 7. Re: How to avoid the error - "ORA-00001: unique constraint violated"
    730428 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Decides to wear his "Just say NO!" t-shirt next meeting ;)
1 2 Previous Next

Legend

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