Re: Disabling Fkey Constraint & there of ..
Date: 1996/04/25
Message-ID: <4lo6sj$mlv_at_inet-nntp-gw-1.us.oracle.com>
> 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)) columnsfrom 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)) columnsfrom 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