Update Table with Primary Key Constraint [message #385732] |
Tue, 10 February 2009 20:14  |
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!
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
|
|
|
|
|