Question: I need a query to find all
tables that reference my table. I tried to delete my table but I
got a foreign key constraint error:
Evidently this is an Oracle DELETE TABLE constraint violation.
How do I find all tables that reference my table?
Answer: I have complete notes on using the
drop table command.
Also see my notes on script
to display all objects related to an Oracle table.
Also see my notes on
You can check for foreign key constraint errors by looking at the
dba_constraints and the dba_cons_columns views.
Using the cascade constraints clause in a drop
table will force a cascade delete to occur in all child
Oracle also has the
table if exists feature.
There are several data dictionary views that allow you to find
all foreign reference to an Oracle table, namely the
dba_constraints and dba_cons_columns views.
Here is one script to display all references to a table using a
This SQL will show all dependent tables for a
table, all reference to the table within other tables:
cons.table_name as child_table,
BC Oracle code depot
for alternate scripts.
cons.table_name = '<TABLE NAME>'
You can also run a query to
find all tables within a PL/SQL procedure.
There are many other ways to see all related tables and you can
download working scripts at the
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.