Saturday, June 18, 2011

Simple script which generates disable/enable foreign keys of child tables

When we need to truncate tables that have child tables with foreign keys enabled, first we should disable all foreign keys. Here is the script that generates SQL commands to disable and enable foreign keys. Enable constraint script is useful to enable them back after truncating.

  select 'alter table ' ||
         child_tab_cons.owner || '.' || child_tab_cons.table_name ||
         ' disable constraint ' ||
         -- ' enable constraint ' ||
         child_tab_cons.constraint_name || ';'
    from all_constraints   parent_tab_cons,
         all_constraints   child_tab_cons
   where parent_tab_cons.owner            = 'MURTY'
     and parent_tab_cons.table_name       in ('TAB1', 'TAB2', 'TAB3')
     and parent_tab_cons.constraint_type  in ('P', 'U')
     and child_tab_cons.r_constraint_name = parent_tab_cons.constraint_name
order by child_tab_cons.owner,

No comments:

Post a Comment