Change primary key value in Oracle

I want to modify the value of a primary key (PK) but there are foreign keys (FK) with data.

You will need to:
– Disable the foreign key constraints
– Change the values of the foreign keys
– Change the primary key values
– Re-enable the foreign key constraints.

The difficulty here is to quickly find all the foreign keys and disable them.

Here is how to disable the foreign key for all the dependant tables? Here is a script to automatically generate the SQL statements. Just change “my_table” and “my_column”

select ‘alter table ‘ || table_name || ‘ disable constraint ‘ || constraint_name || CHR(13) || ‘– my comment’ || CHR(13) || ‘;’ from user_constraints
where table_name in (select
src_cc.table_name as src_table
from
all_constraints c
inner join all_cons_columns dest_cc on
c.r_constraint_name = dest_cc.constraint_name
and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on
c.constraint_name = src_cc.constraint_name
and c.owner = src_cc.owner
where
c.constraint_type = ‘R’
and dest_cc.owner = ‘DI’
and dest_cc.table_name = ‘my_table’
and dest_cc.column_name = ‘my_column’)
and constraint_name in (select
c.constraint_name
from
all_constraints c
inner join all_cons_columns dest_cc on
c.r_constraint_name = dest_cc.constraint_name
and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on
c.constraint_name = src_cc.constraint_name
and c.owner = src_cc.owner
where
c.constraint_type = ‘R’
and dest_cc.owner = ‘DI’
and dest_cc.table_name = ‘my_table’
and dest_cc.column_name = ‘my_column’);

To re-enable the contraints replace “disable” with “enable”.

 

Author: Vincent Fenoll – Oracle DBA in Montreal (Canada)