Problem solve Get help with specific problems with your technologies, process and projects.

Find all the 'son' constraints

Here's a script that gives the "Father" table name and all of its "Sons".

When you want to drop a table, you typically first disable all the FKs to it. So here's a little script that gives you the "Father" table name and all of its "Sons." It has been tested on 8.1.5, 8.1.6 and 8.1.7 Oracle Server.

I use this script when I do database maintenance such as exporting a table, dropping it, and then exporting it back in order to improve performance, save disk space, etc by putting it on one extent. In this case, I want only to export one table; if it has FKs pointing to it, I'll get the "ORA-02449: unique/primary keys in table referenced by foreign keys" error and I have to drop/disable all FKs pointing to it. (It's recommended that you save those FK in order to rebuild them after the export is done.) Here's the script:

select father.TABLE_NAME father_TABLE_NAME,
father.CONSTRAINT_NAME father_CONSTRAINT_NAME ,
        sun.TABLE_NAME sun_TABLE_NAME,
        sun.CONSTRAINT_NAME sun_CONSTRAINT_NAME
 from DBA_CONSTRAINTS sun, DBA_CONSTRAINTS father
 where sun.R_CONSTRAINT_NAME = father.CONSTRAINT_NAME
 and   sun.TABLE_NAME != father.TABLE_NAME
 and   father.TABLE_NAME = upper('&Father_Table_Name')

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was last published in August 2002

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close