Change primary key value in Oracle

Question

Is there a way to change the value of a primary key which is referenced by another table as foreign key?

Solution 1

An easier alternative is to insert a new row and delete the old one. (Update any referencing rows in other tables before you do the delete)

Solution 2

There isn't an in-built UPDATE CASCADE if that's what you're after. You'd need to do something like disable any FK constraints; run UPDATE statements; re-enable the constraints.

Note that updating Primary Keys is (usually always) a bad idea.

Solution 3

You will need to disable the foreign key constraints before changing the primary key values, and then re-enable them afterwards.

If you actually want to implement "update cascade" functionality instead then see Tom Kyte's Update Cascade package

Solution 4

It is possible even without disabling constraints, in case if you would like only to swap keys (which is also a change's subset, so it might be still answer to your question). I wrote an example here: https://stackoverflow.com/a/26584576/1900739

update MY_TABLE t1
set t1.MY_KEY = (case t1.MY_KEY = 100 then 101 else 100 end)
where t1.MYKEY in (100, 101)

View more solutions