Query a Table's Foreign Key relationships

Question

For a given table 'foo', I need a query to generate a set of tables that have foreign keys that point to foo. I'm using Oracle 10G.

Answer 1

This should work (or something close):

select table_name
from all_constraints
where constraint_type='R'
and r_constraint_name in 
  (select constraint_name
  from all_constraints
  where constraint_type in ('P','U')
  and table_name='<your table here>');

Answer 2

This article may help: http://www.databasejournal.com/features/oracle/article.php/3665591

Answer 3

Download the Oracle Reference Guide for 10G which explains the data dictionary tables.

The answers above are good but check out the other tables which may relate to constraints.

select * from dict where table_name like '%CONS%';

Finally, get a tool like Toad or SQL Developer which allows you to browse this stuff in a UI, you need to learn to use the tables but you should use a UI also.

Answer 4

link to Oracle Database Online Documentation

You may want to explore the Data Dictionary views. They have the prefixes:

• User
• All
• DBA

sample:

select * from dictionary where table_name like 'ALL%'

---

Continuing Mike's example, you may want to generate scripts to enable/disable the constraints. I only modified the 'select' in the first row.

select  'alter table ' || TABLE_NAME || ' disable constraint ' || CONSTRAINT_NAME || ';'
from all_constraints
where constraint_type='R'
and r_constraint_name in 
  (select constraint_name
  from all_constraints
  where constraint_type in ('P','U')
  and table_name='<your table here>');

Answer 5

The following statement should give the children and all of their descendents. I have tested it on an Oracle 10 database.

SELECT level, main.table_name parent, link.table_name child FROM user_constraints main, user_constraints link WHERE main.constraint_type IN ('P', 'U') AND link.r_constraint_name = main.constraint_name START WITH main.table_name LIKE UPPER('&&table_name') CONNECT BY main.table_name = PRIOR link.table_name ORDER BY level, main.table_name, link.table_name