RE: Foreign Key constraint Script

From: Channa, Santhosh, SITS <schanna_at_att.com>
Date: Tue, 18 Jul 2000 11:22:31 -0400
Message-Id: <10562.112274@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01BFF0CB.FD7ABD10
Content-Type: text/plain;

charset="iso-8859-1"

Ashish,

Edit the script and change the owner value and run the scripts. CreateAddRiScript.sql -- creates a script to add ri's back. CreateDropRiScript.sql -- creates a script to drop ri's.

Readme.txt -- tells you the steps to follow to drop a table.

<<CreateAddRiScript.sql>> <<CreateDropRiScript.sql>> <<README.txt>>

Santhosh Babu

Work: 336 698 2377
Home: 336 294 8076
Fax : 336 698 2385

<<...OLE_Obj...>>

> ----------
> From: Ashish Shah[SMTP:ar_shah_at_yahoo.com]
> Sent: Monday, July 17, 2000 12:31 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Foreign Key constraint Script
>
> Hi All,
>
> Does any one have any script that will tell me
> a Table has been referenced (foreign key) in how many
> other tables. I had a tough time droping a table
> since
> it was refered by so many other table.
>
> I tried looking user_constraints but it doesn't give
> you information about primary table.
>
> TIA.
>
>
>
>
>
> =====
> Ashish
> Toronto, Canada
>
> __________________________________________________
> Do You Yahoo!?
> Get Yahoo! Mail - Free email you can access from anywhere!
> http://mail.yahoo.com/
> --
> Author: Ashish Shah
> INET: ar_shah_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

------_=_NextPart_000_01BFF0CB.FD7ABD10
Content-Type: application/octet-stream;

name="CreateAddRiScript.sql"
Content-Disposition: attachment;

filename="CreateAddRiScript.sql"

set serveroutput on size 10000
set verify off
prompt

prompt     /********************************************************************************
prompt      ***  This will create a file called AddRiScript.sql under c:\temp\ directory ***
prompt      ********************************************************************************/

prompt
prompt Enter the table name which u want to drop : accept tname
spool c:\temp\AddRiScript.sql

Declare
pk_cons_name varchar2(100);
pk_tab varchar2(30);
refcols varchar2(100);
fkeys varchar2(100);
drule varchar2(20);

cursor pk_or_uk is
select constraint_name main_cons_name, table_name -- into pk_cons_name, pk_tab

from all_constraints
where owner = 'IBSDBA'

     and constraint_type in ('P', 'U')
     and table_name = upper('&TName');

cursor fk_cur(cons_name varchar2) is
select table_name, constraint_name from all_constraints where owner = 'IBSDBA'
and r_constraint_name = cons_name;

cursor cols(cons_name varchar2) is
  select column_name
    from all_cons_columns
   where constraint_name = cons_name
   order by position;

Begin

for main in pk_or_uk loop

for pkcols in cols(main.main_cons_name) loop refcols := refcols||pkcols.column_name||','; end loop;

refcols := substr(refcols,1,length(refcols)-1);

for fk in fk_cur(main.main_cons_name) loop

for fkcols in cols(fk.constraint_name) loop fkeys := fkeys||fkcols.column_name||','; end loop;

fkeys := substr(fkeys,1,length(fkeys)-1); select decode(delete_rule,'CASCADE',' ON DELETE CASCADE ',null)

      into drule
      from all_constraints 
     where constraint_name = fk.constraint_name;

    dbms_output.put_line('Alter table '||fk.table_name||' add constraint '||
	fk.constraint_name||' foreign key ( '||fkeys||' ) references '||
        main.table_name||'( '||refcols||' ) '||drule||';');

fkeys := null;

  end loop;
    refcols:=null;
  end loop;
end;
/
spool
spool off
------_=_NextPart_000_01BFF0CB.FD7ABD10
Content-Type: application/octet-stream;

name="CreateDropRiScript.sql"
Content-Disposition: attachment;

filename="CreateDropRiScript.sql"

set pages 0
set head off
set pau off
set serveroutput on size 10000
set verify off
prompt

prompt     /********************************************************************************
prompt      ***  This will create a file called DropRiScript.sql under c:\temp\ directory ***
prompt      ********************************************************************************/

prompt
prompt Enter the table name which u want to drop : accept tname
spool c:\temp\DropRiScript.sql
select 'alter table '||b.table_name||' drop constraint '||b.constraint_name||' ;' from all_constraints a, all_constraints b

 where a.owner='IBSDBA'
   and a.constraint_name = b.r_constraint_name 
   and a.table_name = upper('&TName')
   and a.constraint_type in ('P','U')

/
spool
spool off
set pages 40
set head on
set pau on
------_=_NextPart_000_01BFF0CB.FD7ABD10
Content-Type: text/plain;

name="README.txt"
Content-Disposition: attachment;

filename="README.txt"

Create a temp table for which ur trying 2 drop. SQL> create table temp_tablename as select *from tablename;
Before going to drop a table first run "CreateDropRiScript.sql". This will create a file called "DropRiScript.sql" under "c:\temp\".
Run "CreateAddRiScripts.sql". This will create a file called "AddRiScript.sql" under "c:\temp\".
Now run "C:\temp\DropRiScript.sql" -- This will drop all RI's basically all child constraints.
Now run the ddl script which has the table modifications. ex. If you are adding a new column to client table then you have to run CLIENT.SQL. This ddl script will actually drops the existing table and recreates.
After creating this table copy all the data back from the temp table created at first step.
After adding the data into this table, run "C:\temp\AddRiScript.sql" -- This will Add all RI's back.

Received on Tue Jul 18 2000 - 10:22:31 CDT