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.

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