3

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.

0

1 Answer 1

5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.