|Update Table with Primary Key Constraint [message #385732]
||Tue, 10 February 2009 20:14
Registered: November 2008
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?
CREATE TABLE MY_TABLE
CODE VARCHAR2(5 BYTE) NOT NULL,
DESCRIPTION VARCHAR2(20 BYTE) NOT NULL
CREATE UNIQUE INDEX MY_TABLE_PK ON MY_TABLE
CREATE INDEX MY_TABLE_DESC_IDX ON MY_TABLE
ALTER TABLE MY_TABLE ADD (
IF v_maddress.filename = 'DBSTA' then
if LTRIM(v_maddress.event) = 'U'then
set CODE = v_maddress.Code1,
DESCRIPTION = v_maddress.Description1;
Encountered this error code:
ORA-00001: unique constraint (MY_SHEMA.MY_TABLE_PK) violated
ORA-06512: at line 54
Searched more about the error:
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.
Any other options? Good day!
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...
[Updated on: Wed, 11 February 2009 00:52] by Moderator
Report message to a moderator