Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid the error - "ORA-00001: unique constraint violated"
How to avoid the error - "ORA-00001: unique constraint violated" [message #434110] Mon, 07 December 2009 03:54 Go to next message
chirpl
Messages: 3
Registered: December 2009
Location: Rome
Junior Member
I've these tables:

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
LUISS_SAM99...009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........1
LUISS_SAM99...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
LUISS_SAM99...009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........1
LUISS_SAM99...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
LUISS_SAM99...009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........1
LUISS_SAM99...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
LUISS_SAM99...009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........1
LUISS_SAM99_1...009345........TOMMY..............SIMPSON.............XXX@bb.com..........LONDON........0
LUISS_SAM99...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!





Re: How to avoid the error - "ORA-00001: unique constraint violated" [message #434114 is a reply to message #434110] Mon, 07 December 2009 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an exception clause that traps this error and modifies the key consequently.

Regards
Michel

[Updated on: Mon, 07 December 2009 04:03]

Report message to a moderator

Re: How to avoid the error - "ORA-00001: unique constraint violated" [message #434119 is a reply to message #434114] Mon, 07 December 2009 04:12 Go to previous messageGo to next message
chirpl
Messages: 3
Registered: December 2009
Location: Rome
Junior Member
Michel Cadot wrote on Mon, 07 December 2009 11:03
Use an exception clause that traps this error and modifies the key consequently.

Regards
Michel



What kind of exception traps this error?
Re: How to avoid the error - "ORA-00001: unique constraint violated" [message #434121 is a reply to message #434119] Mon, 07 December 2009 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to define one with the pragma exception_init

Regards
Michel
Re: How to avoid the error - "ORA-00001: unique constraint violated" [message #434124 is a reply to message #434121] Mon, 07 December 2009 05:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Catch it with the named DUP_VAL_ON_INDEX exception
Re: How to avoid the error - "ORA-00001: unique constraint violated" [message #434126 is a reply to message #434124] Mon, 07 December 2009 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, didn't remember it has a predefined name.

Regards
Michel
Re: How to avoid the error - "ORA-00001: unique constraint violated" [message #434131 is a reply to message #434126] Mon, 07 December 2009 06:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I had to look it up too.

Hint to the OP - your PK structure is wrong - you shouldn't make a PK value dependent on data like you have, because it causes exactly the sort of problems that you're getting.
Previous Topic: Help with incrementing with ROWNUM
Next Topic: Table Space (merged)
Goto Forum:
  


Current Time: Mon Sep 26 11:11:34 CDT 2016

Total time taken to generate the page: 0.15779 seconds