Cascade primary key update to all referencing foreign keys in oracle

cascadeoracleoracle-11gplsql

Is it possible to update all referencing foreign keys in Oracle when updating primary key?

A procedure like this that gets the table name and primary key and finds all foreign keys in all tables and update that with the new value 

  PROCEDURE cascade_update_primary(
                          table_name IN VARCHAR2,
                          primary_column IN Number,
                          new_value IN Number);

Oracle does not support ON UPDATE CASCADE.

Best Answer

With DEFERRABLE foreign key constraints:

create or replace procedure cascade_update_primary
(
  p_table_name in varchar2,
  p_old_value in number,
  p_new_value in number
)
as
  l_column_name varchar2(128 char);
begin
  select
    cc.column_name into l_column_name
  from
    user_cons_columns cc
    join user_constraints c on (c.constraint_name = cc.constraint_name)
  where
    c.constraint_type = 'P' and c.table_name = p_table_name
  ;

  execute immediate 'set constraints all deferred';

  execute immediate 'update "' || p_table_name || '" set "' || l_column_name || 
    '" = :B1 where "' || l_column_name || '" = :B2' using p_new_value, p_old_value;

  for c in (
    select
      cc2.table_name, cc2.column_name
    from
      user_cons_columns cc
      join user_constraints c on (c.constraint_name = cc.constraint_name)
      join user_constraints c2 on (c.constraint_name = c2.r_constraint_name)
      join user_cons_columns cc2 on (c2.constraint_name = cc2.constraint_name)
    where
      c.constraint_type = 'P' and c.table_name = p_table_name
      and cc.column_name = l_column_name
  )
  loop
    execute immediate 'update "' || c.table_name || '" set "' || c.column_name || 
      '" = :B1 where "' || l_column_name || '" = :B2' using p_new_value, p_old_value;
  end loop;
end;
/

In case of NOT DEFERRABLE foreign keys, with some modifications:

  1. insert parent row with new PK value
  2. update child tables to new value
  3. delete parent row with old PK value
Related Question