Oracle Foreign Keys to Drop
I have a problem where I need to drop certain tables and Create replacements (schema additions/deletions to the tables).
Because I have Grandfather-Father-Son table type relationships I have Foreign Keys between them. I need to drop the Foreign Keys but if I build a script to SELECT "ALTER TABLE {TableName} DROP CONTRAINT {ConstraintName}" FROM USER_CONSTRAINTS the order matters where it chooses the Father table for example first.
If I change the script being built to have "...{ConstraintName} CASCADE" whilst it runs succesfully it ends up with hundreds of ORA-02443 Errors because the constraint has already been removed by an earlier "ALTER TABLE" command.
Is there a way that I can tell which tables are the Grandfathers and just include those CASCADE'd in my selection? That way presumably, all the Foriegn Keys will be deleted without any overlying superflous errors.
Many thanks,
Software/Hardware used:
Software/Hardware used:
ASKED:
August 18, 2005 3:36 AM
UPDATED:
August 19, 2005 12:30 PM
Answer Wiki:
Hi,
Just a thought. There are R and P types of constraint.
If you use - select a.table_name from user_constraints a where a.constraint_type ='R' and a.table_name not in (select b.table_name from user_constraints b where b.constraint_type = 'P')
you might get all lowest child tables.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
