Is it possible to modify the value of a record's primary key in Oracle when child records exist?
oracle, sql
Solution
There are no cascading updates.
You could use a deferrable constraint.
Or, within a transaction:
Copy the Parent to a new row with the new key:
INSERT INTO Parent (key, cols...) SELECT newkey, cols... FROM Parent WHERE key = oldkey
Assign all the children:
UPDATE Child SET parent_id = newkey WHERE parent_id = oldkey
Delete the parent now that no one is referencing it:
DELETE FROM Parent WHERE key = oldkey
See this link.
Problem
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?