Re: script to dynmically create all foreign key constraints

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Wed, 11 Dec 2002 18:14:51 GMT
Message-ID: <vgLJ9.3426$TH.95190361@newssvr21.news.prodigy.com>

Steve wrote:
> hey there,
> anyone have a script handy that generates sql to create all the
> foreign key's in the database? Our client does not like ON DELETE
> CASCADE to be left on. However, once in a while we need to use that
> functionlity so I as going to drop the FK's in restricted session,
> recreate them with ON DELETE CASCADE, perform the operation what
> requires DELETE CASCADE, and then drop and recreate the FK's without
> ON DELETE CASCADE. I know this sounds kind of hokey but the ON DELETE
> CASCADE functionality is only ever required once every odd month so. I
> know I can get the constraints from import export but I'd like a
> dynaminc sql that I can just edit one and run.
The following lists all the PK/FK constraints. You can probably modify the routine to generate the ALTER TABLE commands:

set echo off feedback off pause off pagesize 0 set serveroutput on size 100000

DECLARE
   --
   cursor c_user_tables is

     select table_name
     from   user_tables
     order by table_name;

   --
   cursor c_user_constraints ( vin_table_name in varchar2 ) is

     select table_name,
            constraint_type,
            constraint_name,
            r_constraint_name,
            decode ( delete_rule,
              'CASCADE', '-YES-',
              'No'
            )                      decode_delete_rule,
            status
     from   user_constraints
     where  constraint_type in ('P', 'R')
     and    table_name = vin_table_name
     order by table_name, constraint_type, constraint_name;

   --
   cursor c_user_cons_columns ( vin_constraint_name in varchar2 ) is

     select constraint_name,
            column_name
     from   user_cons_columns
     where  constraint_name = vin_constraint_name
     order by position;

   --
BEGIN
   --
   dbms_output.enable ( 100000 );
   --

• Display header. dbms_output.put_line ( chr(10) || 'Primary/Foreign Key Constraint Layout Listing' ); --
• For each table... for r_user_tables in c_user_tables loop --
  • Display table name. dbms_output.put_line ( chr(10) || 'Table: ' || r_user_tables.table_name ); --
  • For each constraint... for r_user_constraints in c_user_constraints ( r_user_tables.table_name ) loop --
    • Indicate whether Primary or Foreign key. if r_user_constraints.constraint_type = 'P' then dbms_output.put ( '..Primary Key: ' ); else dbms_output.put ( '..Foreign Key: ' ); end if; --
    • Display constraint attributes. dbms_output.put_line ( r_user_constraints.constraint_name || ' (' || r_user_constraints.status || ')' ); --
    • If FK, display PK of parent. if r_user_constraints.constraint_type = 'R' then dbms_output.put_line ( '....Cascade Del: ' || r_user_constraints.decode_delete_rule ); -- dbms_output.put_line ( '....Parent (PK): ' || r_user_constraints.r_constraint_name ); end if; --
    • For each constraint column... for r_user_cons_columns in c_user_cons_columns ( r_user_constraints.constraint_name ) loop -- dbms_output.put_line ( '....Column: ' || r_user_cons_columns.column_name ); -- end loop; -- End for each constraint column... -- end loop; -- End for each constraint... -- end loop; -- End for each table... -- END; /

set serveroutput off
set feedback on pagesize 24 Received on Wed Dec 11 2002 - 12:14:51 CST