Home » SQL & PL/SQL » SQL & PL/SQL » Update Table with Primary Key Constraint (Oracle Database 10g Express Edition Release 10.2.0.1.0 )
Update Table with Primary Key Constraint [message #385732] Tue, 10 February 2009 20:14 Go to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi Experts,

This is probably a trivial question for you, but I have only encountered this now, i thought this will be just an ordinary update of a record..

How can I update a record with a primary key constraint?

MY_TABLE:

CREATE TABLE MY_TABLE
(
  CODE         VARCHAR2(5 BYTE)                 NOT NULL,
  DESCRIPTION  VARCHAR2(20 BYTE)                NOT NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX MY_TABLE_PK ON MY_TABLE
(CODE)
LOGGING
NOPARALLEL;


CREATE INDEX MY_TABLE_DESC_IDX ON MY_TABLE
(DESCRIPTION)
LOGGING
NOPARALLEL;


ALTER TABLE MY_TABLE ADD (
  CONSTRAINT MY_TABLE_PK
 PRIMARY KEY
 (CODE));


Sample Code:

.
.

 IF v_maddress.filename = 'DBSTA' then
	  if LTRIM(v_maddress.event) = 'U'then
	  
	   UPDATE MY_TABLE
	    set CODE = v_maddress.Code1,
		DESCRIPTION = v_maddress.Description1;
 ELSIF
.
.


Encountered this error code:

ORA-00001: unique constraint (MY_SHEMA.MY_TABLE_PK) violated
ORA-06512: at line 54


Searched more about the error:

Quote:

ORA-00001: unique constraint (string.string) violated
Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.
Action: Either remove the unique restriction or do not insert the key.

-- I cant modify of drop its table constraints..



Searched on this forum and found this, does my solution has to do with the following? But I dont think so..?

rleishman wrote on Mon, 09 February 2009 19:56
ON DELETE CASCADE means when you delete the parent of a foreign key relationship, the chilren are deleted too. It is supported natively by Oracle - you can specify it when you create a foreign key constraints

ON UPDATE CASCADE means that when you update the PRIMARY KEY on the parent of a foreign key relationship, the FOREIGN KEY of the child is also updated. It is NOT supported natively by Oracle - you must code it yourself using triggers.

Ross Leishman


Any other options? Good day! Smile

PS: Session expired... Checked if its already posted on the board, but it still isn't, so reposting again, hope it wont create a duplicate thead...

Regards,
Wilbert

[Updated on: Wed, 11 February 2009 00:52] by Moderator

Report message to a moderator

Re: Update Table with Primary Key Constraint [message #385733 is a reply to message #385732] Tue, 10 February 2009 20:24 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>How can I update a record with a primary key constraint?
It can be changed, but only to a value which does not currently exist within this table.
Re: Update Table with Primary Key Constraint [message #385738 is a reply to message #385732] Tue, 10 February 2009 22:13 Go to previous message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Resolved.. Sorry about that, but I guessed I learned something new today..
Need more coffee to think clearly.. Laughing

  UPDATE MY_TABLE
   set DESCRIPTION = v_maddress.Area_Code
   WHERE CODE = v_maddress.District_Code;

[Updated on: Tue, 10 February 2009 22:14]

Report message to a moderator

Previous Topic: translate data trunction problem
Next Topic: ORA-21560: while size is less than 1 GB for a CLOB
Goto Forum:
  


Current Time: Fri Dec 09 11:58:33 CST 2016

Total time taken to generate the page: 0.12640 seconds