Query a Table's Foreign Key relationships

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 

Here's how to take Mike's query one step further to get the column names from the constraint names:

select * from user_cons_columns where constraint_name in ( select 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 name here>')); 

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>'); 

I know it's kinda late to answer but let me answer anyway. Some of the answers above are quite complicated hence here is a much simpler take.

SELECT a.table_name child_table, a.column_name child_column, a.constraint_name, b.table_name parent_table, b.column_name parent_column FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name join all_cons_columns b on c.owner = b.owner and c.r_constraint_name = b.constraint_name WHERE c.constraint_type = 'R' AND a.table_name = 'your table name' 

select distinct table_name, constraint_name, column_name, r_table_name, position, constraint_type from ( SELECT uc.table_name, uc.constraint_name, cols.column_name, (select table_name from user_constraints where constraint_name = uc.r_constraint_name) r_table_name, (select column_name from user_cons_columns where constraint_name = uc.r_constraint_name and position = cols.position) r_column_name, cols.position, uc.constraint_type FROM user_constraints uc inner join user_cons_columns cols on uc.constraint_name = cols.constraint_name where constraint_type != 'C' ) start with table_name = '&&tableName' and column_name = '&&columnName' connect by nocycle prior table_name = r_table_name and prior column_name = r_column_name; 

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.

select acc.table_name, acc.constraint_name from all_cons_columns acc inner join all_constraints ac on acc.constraint_name = ac.constraint_name where ac.r_constraint_name in ( select constraint_name from all_constraints where table_name='yourTable' ); 

All constraints for one table

select uc.OWNER, uc.constraint_name as TableConstraint1, uc.r_constraint_name as TableConstraint2, uc.constraint_type as constrainttype1, us.constraint_type as constrainttype2, uc.table_name as Table1,us.table_name as Table2, ucc.column_name as TableColumn1, uccs.column_name as TableColumn2 from user_constraints uc left outer join user_constraints us on uc.r_constraint_name = us.constraint_name left outer join USER_CONS_COLUMNS ucc on ucc.constraint_name = uc.constraint_name left outer join USER_CONS_COLUMNS uccs on uccs.constraint_name = us.constraint_name where uc.OWNER ='xxxx' and uc.table_name='xxxx' 

Adding my two cents here.

This query will return all foreign keys with child and parent columns, matched perfectly even when there is foreign key over multiple columns:

SELECT a.table_name child_table, a.column_name child_column, a.constraint_name, b.table_name parent_table, b.column_name parent_column FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_cons_columns b ON c.owner = b.owner AND c.r_constraint_name = b.constraint_name AND b.position = a.position WHERE c.constraint_type = 'R' 

(inspired by @arvinq aswer)