Re: Disabling Fkey Constraint & there of ..

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/04/25
Message-ID: <4lo6sj$mlv_at_inet-nntp-gw-1.us.oracle.com>


jp_at_queen.plaza.nt.com (JP Srinath) wrote:

>I think you are missing the point here. Let me try to explain what I am
> trying to do.

 

> I want to make some some structural changes to the parent table - So I
> intend to export the data, disable Fkey of the children, drop the parent
> , recreate the parent, import data & enable the Fkey of the children.
 

> Why should the DBMS stop me from dropping the parent ? I know very well
> that the children are hanging in the air - but at the same time I can
> make sure that all the Fkey's are present in the parent before I enable
> the Fkey constraint !
 

> The point here is, I need to do more typing in order to do this
> because, it seems that the only way I can do it is by 'droping the table
>with cascade constraints' (as you suggested) - and recreating the constraints
 

> Is this the only way to accomplish the task in other environments like db2
> etc., ?
 

> I can understand the reasons behind this, but at the same time I need
> to type more u know ????

> Thanks ,
 

> JP

Ok, ok, ok. Here is a sqlplus script. Say you have a table T. It is a parent table. You are going to drop and rebuild table T. Table T is referenced by n other tables and you don't want to have to rebuild the fkey constraints on these n different tables yourself. Then, BEFORE you drop table T run the following sql*plus script:

SQL> _at_cons T

  • cons.sql ------------------------------ 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') /

spool the output of this to a file. This will create the alter CHILD_TABLE add constraint CONS_NAME foreign key ( FKEY-LIST ) references PARENT_TABLE ( PKEY-LIST ) statements for you. Then you may drop and recreate the parent table and as long as the columns you needed for the fkey constraint still exist, the spooled output will recreate the foreign keys for you.

No more typing.

As an 'added' bonus, I am posting a script to seek out and report on unindexed foreign keys.

  • unindex.sql --------------------------------- column columns format a20 word_wrapped column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,

           a.table_name, a.columns, b.columns
from
( select a.table_name, a.constraint_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)) 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 ) a, ( select table_name, index_name,
	     max(decode(column_position, 1,     column_name,NULL)) || 
	     max(decode(column_position, 2,', '||column_name,NULL)) || 
	     max(decode(column_position, 3,', '||column_name,NULL)) || 
	     max(decode(column_position, 4,', '||column_name,NULL)) || 
	     max(decode(column_position, 5,', '||column_name,NULL)) || 
	     max(decode(column_position, 6,', '||column_name,NULL)) || 
	     max(decode(column_position, 7,', '||column_name,NULL)) || 
	     max(decode(column_position, 8,', '||column_name,NULL)) || 
	     max(decode(column_position, 9,', '||column_name,NULL)) || 
	     max(decode(column_position,10,', '||column_name,NULL)) || 
	     max(decode(column_position,11,', '||column_name,NULL)) || 
	     max(decode(column_position,12,', '||column_name,NULL)) || 
	     max(decode(column_position,13,', '||column_name,NULL)) || 
	     max(decode(column_position,14,', '||column_name,NULL)) || 
	     max(decode(column_position,15,', '||column_name,NULL)) || 
	     max(decode(column_position,16,', '||column_name,NULL)) columns
    from user_ind_columns
   group by table_name, index_name ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%' /

If the above queries don't work for you (version 7.0 users, will work in 7.1 and up) simply remove the inline views from the 'FROM' clause, create as permanent views in their place and reference the permanent views in the sql statement.

You could of course change this to use the ALL_* tables instead of USER_*. The above scripts only work if the referencing tables are in the same schema as the parent table and you are logged into that schema.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government



opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Thu Apr 25 1996 - 00:00:00 CEST

Original text of this message