Defying Referential Integrity

Updating a Primary Key value without dropping Foreign key constraints in Oracle 8i and later versions

By: Dhaval Somaiya/Kunal Mehta

Manier times there are cases wherein a primary key value needs an update, But  updating  them in Oracle would be a tricky thing if you do have foreign key.

Trying to update a primary key will throw an error “Child record found”.

Similarly, trying to update a foreign key will throw an error “Parent record found”. 

Following steps guides you to overcome these hurdles while making sure to have referential integrity intact.

Step 1:

Assume a Primary key and a foreign key relationship.

ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPARTMENT_ID);

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMP FOREIGN KEY (DEPARTMENT _ID) REFERENCES DEPARTMENT (DEPARTMENT_ID) ;

It is essential to create the constraint as DEFERRABLE right away when creating it, if you want to be able to differ the constraint check to the end of the transaction.

DEFERRABLE constraint is IMMEDIATE by default.

Step 2:

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMP FOREIGN KEY (DEPARTMENT _ID) REFERENCES DEPARTMENT (DEPARTMENT_ID) DEFERRABLE;

You can check the same using the following query.

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME=’ EMPLOYEE’

Step 3: ALTER SESSION SET CONSTRAINTS=DEFERRED;

Step 4: ALTER TABLE DEPARTMENT SET DEPARTMENT_ID=DEPARTMENT_ID+1;

Step 5: ALTER TABLE EMPLOYEE SET DEPARTMENT_ID=DEPARTMENT_ID+1;

Step 6: ALTER SESSION SET CONSTRAINTS=IMMEDIATE

Step 7: EXECUTE COMMIT;