Oracle Foreign Keys to Drop

pts.
Tags:
Microsoft Windows
Oracle
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,

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • NigelT
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/oracle-foreign-keys-to-drop/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following