CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle10g
Mark Roddy
Mark Roddy

Reputation: 27936

Query a Table's Foreign Key relationships

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.

Upvotes: 26

Views: 66118

Answers (10)

Gh61
Gh61

Reputation: 9736

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)

Upvotes: 0

arvinq
arvinq

Reputation: 699

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'

Upvotes: 1

Mehmet Kurt
Mehmet Kurt

Reputation: 161

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'

Upvotes: 0

Hiram
Hiram

Reputation: 467

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

Upvotes: 0

Ethan Post
Ethan Post

Reputation: 3050

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.

Upvotes: 0

Abu Turab
Abu Turab

Reputation: 61

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;

Upvotes: 0

matt1616
matt1616

Reputation: 502

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

Upvotes: 4

richard.j.lewis
richard.j.lewis

Reputation:

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

Upvotes: 5

Tony R
Tony R

Reputation: 7336

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

Upvotes: 1

Mike Monette
Mike Monette

Reputation: 640

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

Upvotes: 46

Related Questions