Drop and Recreate All Foreign Keys on Oracle Table

The last post showed a workaround for the error “ORA-02449: unique/primary keys in table referenced by foreign keys“, which blocks any attempt to drop an Oracle table if there are foreign keys from other tables referring to it.

The caveat: if the dropped table has to be recreated, the dropped foreign keys must be recreated as well.

How will you determine which foreign keys are to be recreated, and how will you quickly create them? Read on for an easy solution.

The steps to follow:

Step 1. Before dropping the table, say TABX, referenced by foreign keys, use two SQLs to generate the following SQL scripts:

    1.1 SQL1 to generate a "drop constraint" script (dropcons) to drop all constraints that depend on TABX.  

1.2 SQL2 to generate an "add constraint" script (addcons) to add all the dropped constraints on TABX.   

Step 2. Run dropcons to drop constraints referencing the table TABX.

Step 3. Drop and create the table TABX with the changes as required.

Step 4. Run addcons to add back the dropped constraints referencing TABX.

Let us see an example with the DEPARTMENTS table of the default HR schema in Oracle XE.

Step 1: Generate drop/add constraint scripts via SQL

This is done by writing an SQL statement in such a way that its output forms another SQL statement or command. The statements for DEPARTMENTS table are:

1.1 dropcons: "Drop constraint" script generation

-- 1.1 dropcons: This SQL will create a   
-- "drop constraint" script to drop all     
-- constraints that depend on DEPARTMENTS table  
select 'alter table ' 
       || table_name
       || ' drop constraint '
       || constraint_name
       ||';' constraint_disable
from all_constraints
where constraint_type = 'R'
and status = 'ENABLED'
and r_constraint_name in
 (
   select constraint_name 
   from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = 'DEPARTMENTS'
 );  

1.2 addcons: "Add constraint" script generation

Two versions of the script are below – the first is more compact, flexible and faster. Use that unless your Oracle version is older than 11G R2.

11G R2+ solution:

-- 1.2. addcons: This SQL will create an
-- "add constraint" script to recreate all
-- constraints that depend on DEPARTMENTS table.
-- Compatible with Oracle 11G R2 +
select  'alter table ' || t1_table_name
     || ' add constraint ' || t1_constraint_name
     || ' foreign key (' || t1_column_names || ')'
     || ' references ' || t2_table_name
     || '(' || t2_column_names || ');' FK_script
  from
    (select a.table_name t1_table_name
      , a.constraint_name t1_constraint_name
      , b.r_constraint_name t2_constraint_name
      -- Concatenate columns to handle composite
      -- foreign keys
      , listagg(a.column_name,', ') 
            within group (order by a.position) 
            as t1_column_names
    from user_cons_columns a
       , user_constraints b
    where a.constraint_name = b.constraint_name
    and b.constraint_type = 'R'
    group by a.table_name
           , a.constraint_name
           , b.r_constraint_name
    ) t1,
    (select a.constraint_name t2_constraint_name
      , a.table_name t2_table_name
      -- Concatenate columns for PK/UK referenced
      -- from a composite foreign key
      , listagg(a.column_name,', ')
            within group (order by a.position)
            as t2_column_names
    from user_cons_columns a, user_constraints b
    where a.constraint_name = b.constraint_name
    and b.constraint_type in ( 'P', 'U' )
    group by a.table_name
           , a.constraint_name ) t2
where t1.t2_constraint_name = t2.t2_constraint_name
  and t2.t2_table_name = 'DEPARTMENTS';

Pre 11G R2 solution:

-- 1.2. addcons: This SQL will create an
-- "add constraint" script to recreate all
-- constraints that depend on DEPARTMENTS table
select  'alter table ' || t1_table_name
     || ' add constraint ' || t1_constraint_name
     || ' foreign key (' || t1_column_names || ')'
     || ' references ' || t2_table_name
     || '(' || t2_column_names || ');' FK_script
  from
    (select a.table_name t1_table_name
      , a.constraint_name t1_constraint_name
      , b.r_constraint_name t2_constraint_name
      -- Concatenate columns to handle composite
      -- foreign keys [handles up to 5 columns]
      , max(decode(a.position, 1,
           a.column_name,NULL)) ||
        max(decode(a.position, 2,', '||
           a.column_name,NULL)) ||
        max(decode(a.position, 3,', '||
           a.column_name,NULL)) ||
        max(decode(a.position, 4,', '||
           a.column_name,NULL)) ||
        max(decode(a.position, 5,', '||
           a.column_name,NULL))
          t1_column_names
    from user_cons_columns a
       , user_constraints b
    where a.constraint_name = b.constraint_name
    and b.constraint_type = 'R'
    group by a.table_name
           , a.constraint_name
           , b.r_constraint_name
    ) t1,
    (select a.constraint_name t2_constraint_name
      , a.table_name t2_table_name
      -- Concatenate columns for PK/UK referenced
      -- from a composite foreign key
      , max(decode(a.position, 1,
           a.column_name,NULL)) ||
        max(decode(a.position, 2,', '||
           a.column_name,NULL)) ||
        max(decode(a.position, 3,', '||
           a.column_name,NULL)) ||
        max(decode(a.position, 4,', '||
           a.column_name,NULL)) ||
        max(decode(a.position, 5,', '||
           a.column_name,NULL))
          t2_column_names
    from user_cons_columns a, user_constraints b
    where a.constraint_name = b.constraint_name
    and b.constraint_type in ( 'P', 'U' )
    group by a.table_name
           , a.constraint_name ) t2
where t1.t2_constraint_name = t2.t2_constraint_name
  and t2.t2_table_name = 'DEPARTMENTS';

Step 2. Drop constraints referencing the table

Run the script dropcons (output of Step 1.1) to drop constraints referencing the table.

SQL> -- 2. Running the output of 1.1 to drop constraints
SQL> alter table EMPLOYEES drop constraint EMP_DEPT_FK;

Table altered.

SQL> alter table JOB_HISTORY drop constraint JHIST_DEPT_FK;

Table altered.

Step 3: Drop and recreate the referenced table

 SQL> -- 3. Drop and recreate the table
SQL> drop table departments;

Table dropped.

SQL> @create_dept.sql

Table created.

Step 4: Add constraints on other tables referencing the recreated table

Run the script addcons (output of Step 1.2) to drop constraints referencing the table.

SQL> -- 4. Running the output of 1.2 to add constraints
SQL> -- on other tables referencing the recreated table

SQL> alter table EMPLOYEES add constraint EMP_DEPT_FK foreign key (DEPARTMENT_ID) references DEPARTMENTS(DEPARTMENT_ID);

Table altered.

SQL> alter table JOB_HISTORY add constraint JHIST_DEPT_FK foreign key (DEPARTMENT_ID) references DEPARTMENTS(DEPARTMENT_ID);

Table altered. 

That’s it! You have smoothly got all the constraints in place, as if the table had not been dropped at all.