Is it possible to modify the value of a record's primary key in Oracle when child records exist?
- by Chris Farmer
I have some Oracle tables that represent a parent-child relationship. They look something like this:
create table Parent (
parent_id varchar2(20) not null primary key
);
create table Child (
child_id number not null primary key,
parent_id varchar2(20) not null,
constraint fk_parent_id
foreign key (parent_id)
references Parent (parent_id)
);
This is a live database and its schema was designed long ago under the assumption that the parent_id field would be static and unchanging for a given record. Now the rules have changed and we really would like to change the value of parent_id for some records.
For example, I have these records:
Parent:
parent_id
---------
ABC123
Child:
child_id parent_id
-------- ---------
1 ABC123
2 ABC123
And I want to modify ABC123 in these records in both tables to something else.
It's my understanding that one cannot write an Oracle update statement that will update both parent and child tables simultaneously, and given the FK constraint, I'm not sure how best to update my database. I am currently disabling the fk_parent_id constraint, updating each table independently, and then enabling the constraint.
Is there a better, single-step way to update this content?
