Re: Recursive SQL to drop and add Foreign Key constraints

From: Thomas kyte <tkyte_at_us.oracle.com>
Date: 1997/03/25
Message-ID: <3337e6f2.1619078@newshost>#1/1

Here is a query you can run in sqlplus to generate the needed alter table statements. You point it at a parent table and it will generate all the alters for the child tables. It should work in 7.1 and up. You would just spool the output to a file to run later.....

column fkey format a80 word_wrapped
select

'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns || ');'

fkey
from
( select a.table_name child_tname, a.constraint_name child_cons_name,

         b.r_constraint_name parent_cons_name,
         max(decode(position, 1,     '"'||column_name||'"',NULL)) || 
         max(decode(position, 2,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 3,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 4,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 5,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 6,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 7,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 8,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 9,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,10,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,11,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,12,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,13,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,14,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,15,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,16,', '||'"'||column_name||'"',NULL)) 
            child_columns

    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 ) child,
( select a.constraint_name parent_cons_name, a.table_name parent_tname,

         max(decode(position, 1,     '"'||column_name||'"',NULL)) || 
         max(decode(position, 2,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 3,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 4,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 5,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 6,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 7,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 8,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position, 9,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,10,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,11,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,12,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,13,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,14,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,15,', '||'"'||column_name||'"',NULL)) || 
         max(decode(position,16,', '||'"'||column_name||'"',NULL)) 
            parent_columns

    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 ) parent where child.parent_cons_name = parent.parent_cons_name   and parent.parent_tname = upper('&1')
/

On Fri, 21 Mar 1997 09:06:39 -0500, John Hough <q6y_at_ornl.gov> wrote:

>Andy Smith wrote:
>>
>> --
>> I'm going to rebuild some Primary Key (indexes) constraints and
>> need some scripts that will go through the database and produce
>> ALTER TABLE statements for dropping and re-creating the Foreign Key
>> constraints.
>>
>> Does anyone have any scripts to this sort of thing ?
>>
>> Regards
>> =================================================================
>> Andy Smith Email: andy.smith_at_citicorp.com
>
>Andy:
>
>I believe I saw some scripts to do excactly this in the "Code Depot" of
>the Oracle Magazine web page. It is located at URL:
>http://www.oramag.com
>
>Hope this helps,
>
>John Hough

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Mar 25 1997 - 00:00:00 CST