Save your foreign key constraint before dropping table

There might be the case that you find it difficult to recreate the table with another table having foreign keys dependent on it. When you need to recreate the table that mean you need to drop that table first. Apart form losing data and other objects related to that table you will also lost foreign key constraint on every tables which referring to it. There is a little tip top help you get all those foreign key constraints back with an ease, below are a few step to do it.

1. Before dropping the table run the below script which will prompt you to input the table you are about to drop and keep the output.

select 'alter table ' || cc.table_name || ' add constraint ' || c.constraint_name || ' foreign key(' ||
cc.column_name || ') references ' || cc_r.table_name || '( ' || cc_r.column_name || ');'
from user_constraints c, user_cons_columns cc, user_cons_columns cc_r
where c.constraint_type = 'R' and
cc.constraint_name = c.constraint_name and
cc.table_name = c.table_name and
cc_r.constraint_name = c.r_constraint_name and cc_r.TABLE_NAME = '&ref_tab';

2. drop the desired table
3. recreate that table
4. run the output scripts you get from number 1

Posted in: Oracle Tips