Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    227

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

    I've this tables:

    Code:
    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.......FLA G
    LUISS_SAM99...009999........SAMMY..............LUI SS.............ddd@bb.com..........ROME........1
    LUISS_SAM99...009345........TOMMY..............SIM PSON.............XXX@bb.com..........LONDON....... .1
    LUISS_SAM99...009656........JOHN..............KAYN E.............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:

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

    PK_ID................COD_ID.........NAME_FIRST.... .....NAME_LAST.........EMAIL........CITY.......FLA G
    LUISS_SAM99...009999........SAMMY..............LUI SS.............ddd@bb.com..........ROME........1
    LUISS_SAM99_1...009999........SAMMY..............L UISS.............ddd@bb.com..........ROME........0
    LUISS_SAM99...009345........TOMMY..............SIM PSON.............XXX@bb.com..........LONDON....... .1
    LUISS_SAM99...009656........JOHN..............KAYN E.............YYYY@bb.com..........PARIS........1

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

    PK_ID................COD_ID.........NAME_FIRST.... .....NAME_LAST.........EMAIL........CITY.......FLA G
    LUISS_SAM99...009999........SAMMY..............LUI SS.............ddd@bb.com..........ROME........1
    LUISS_SAM99_1...009999........SAMMY..............L UISS.............ddd@bb.com..........ROME........0
    LUISS_SAM99_2...009999........SAMMY..............L UISS.............ddd@bb.com..........ROME........0
    LUISS_SAM99...009345........TOMMY..............SIM PSON.............XXX@bb.com..........LONDON....... .1
    LUISS_SAM99...009656........JOHN..............KAYN E.............YYYY@bb.com..........PARIS........1

    Code:
    execute INS_DUP_COD ('009345');
    
    PL/SQL procedure successfully completed
    My Output Will be:

    PK_ID................COD_ID.........NAME_FIRST.... .....NAME_LAST.........EMAIL........CITY.......FLA G
    LUISS_SAM99...009999........SAMMY..............LUI SS.............ddd@bb.com..........ROME........1
    LUISS_SAM99_1...009999........SAMMY..............L UISS.............ddd@bb.com..........ROME........0
    LUISS_SAM99_2...009999........SAMMY..............L UISS.............ddd@bb.com..........ROME........0
    LUISS_SAM99...009345........TOMMY..............SIM PSON.............XXX@bb.com..........LONDON....... .1
    LUISS_SAM99_1...009345........TOMMY..............S IMPSON.............XXX@bb.com..........LONDON..... ...0
    LUISS_SAM99...009656........JOHN..............KAYN E.............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!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by raf View Post
    How Can I write my stored procedure to avoid "ORA-00001: unique constraint violated" on PK_ID column?
    That's simple: don't insert the same PK_ID twice

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Or use a synthetic key instead of your employee data.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Oh man! Shammat stole my thunder. I really wanted to give that answer.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    well, you could always do the other one, remove the unique constraint

  6. #6
    Join Date
    May 2009
    Location
    India
    Posts
    66
    Or check whether it exists and on this exception reincrement and get another value.

    End

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •