Fun and games with foreign keys in Oracle

Thought I'd post two small scripts I wrote to take care of evil foreign keys when doing data restructuring.

First spool this script to a file to have the create statements of all FK's. The script does a self join to get the referenced column.

create_foreign_keys.sql

set lines 180
set pages 0
set heading off
set feedback off
select distinct
        'alter table ' ||c.table_name||
        ' add constraint '|| c.constraint_name ||
        ' foreign key ('||c.column_name||
        ') references ' || d.table_name ||
        '('|| d.column_name||');'
from
        dba_cons_columns c,
        dba_cons_columns d,
        dba_constraints a
where
        c.owner='SCHEMANAME'
        and c.constraint_name=a.constraint_name
        and a.constraint_type='R'
        and a.R_CONSTRAINT_NAME=d.CONSTRAINT_NAME
/

Then spool and execute this script to drop all constraints.

drop_foreign_keys.sql

set lines 180
set pages 0
set heading off
set feedback off
select distinct
        'alter table ' ||c.table_name||
        ' drop constraint '|| c.constraint_name ||';'
from
        dba_cons_columns c,
        dba_constraints a
where
        c.owner='SCHEMANAME'
        and c.constraint_name=a.constraint_name
        and a.constraint_type='R'
/

After you are done with your restructuring you just execute the first script to re-create all the foreign keys. Done!
Probably doesn't work on to advanced FK's but anyway. :)