Safely Rename Value without Breaking Foreign Key Constraints

I want to rename a value in a table whose main role is as an enumeration. That is, it’s a value used in foreign keys from quite a few other tables.

This example is Oracle-specific. I want to insert my new row as a copy of an existing row.

insert into some_table
  (some_table_id, column_1, column_2)
  select 'NEW VALUE', column_1, column_2
  from some_table
	where some_table_id = 'OLD VALUE';

Then I want to figure out all of the foreign key constraints:

select * from sys.all_cons_columns where column_name='SOME_TABLE_ID' and owner='SOME_OWNER';

For each of the dependent tables, I want to update their rows to point to the new value.

update DEPENDENT_TABLE set SOME_TABLE_ID to 'NEW VALUE' where SOME_TABLE_ID = 'OLD VALUE';

Finally, I want to remove the old value from the original table:

delete from SOME_TABLE where SOME_TABLE_ID = 'OLD VALUE';

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply