Query a Table's Foreign Key relationships
Solution 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>');
Solution 2
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
Solution 3
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>'));
Solution 4
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'
Solution 5
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>');
Related videos on Youtube
Anna
Updated on July 09, 2022Comments
-
Anna almost 2 years
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.
-
Sean over 15 yearsThis article may help: http://www.databasejournal.com/features/oracle/article.php/3665591
-
Sten Vesterli over 15 yearsIf you just need this info in order to drop the table, you can also use DROP TABLE xx CASCADE CONSTRAINTS
-
-
focusHard over 10 yearsnice use of hierarchical retrieval. However when you have tables with self referencing foreign keys, it will generate error.
-
Tom almost 8 yearsThis is the greatest thing ever. You just need to change the 'U' to 'R' for newer versions of Oracle.
-
Tom almost 8 years@focusHard, I believe that adding
AND main.table_name <> link.table_name
to the where clause will prevent that error. -
Ravi Wallau about 7 yearsI think this works better: <pre> select a.table_name child_table, a.column_name child_column, a.constraint_name, b.table_name parent_table, b.column_name parent_column, a.position from user_cons_columns a join user_constraints c on a.owner = c.owner and a.constraint_name = c.constraint_name join user_cons_columns b on c.owner = b.owner and c.r_constraint_name = b.constraint_name and a.position = b.position where c.constraint_type = 'R' order by a.constraint_name, a.table_name, a.column_name, b.table_name, b.column_name
-
Gh61 about 3 yearsWhen the foreing key is over multiple columns, this query returns invalid results.