1

How can I find the parent table(s) of foreign constraint(s) on a table key in oracle? I'm needing to create a dropdown of all of the values that can be selected for this column I'm looking at and need to know the parent so I can look up its sibling values.

Dale
  • 1,613
  • 4
  • 22
  • 42

2 Answers2

2

You can query this information from all_constraints (or user_constraints or dba_constraints, of course). Unfortunately, you can only retrieve the name of the constraint a foreign key refers to, so you'll have to use a sub query or a self join to retrieve the referring table:

SELECT r.table_name
FROM   user_constraints t
JOIN   user_constraints r ON t.r_constraint_name = r.constraint_name
WHERE  t.constraint_type = 'R' AND t.table_name = 'SOME_TABLE'
CL.
  • 173,858
  • 17
  • 217
  • 259
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 1
    So I should apply this to the table that has the constraint? The child table? – Dale Oct 27 '16 at 21:19
  • @Dale yup - replace `'SOME_TABLE'` with the name of the table that has the foreign key constraints (the child table) and you should get the name[s] of the table[s] it references (the parent table[s]) – Mureinik Oct 27 '16 at 21:20
  • I did this and received an error of ORA-00904: "R"."CONSTRAINTS_NAME": invalid identifier – Dale Oct 27 '16 at 21:21
  • @Dale That was a typo, mea culpa. It's supposed to be "constraint_name". I've edited the answer and fixed it. – Mureinik Oct 27 '16 at 21:22
  • Thanks. That did it. – Dale Oct 27 '16 at 21:27
0

You can use the below query to get the parent table.

select * from all_constraints
where constraint_name in (select r_constraint_name from all_constraints
where table_name in 'TAB_NAME');
Kaushal Talniya
  • 182
  • 2
  • 5
  • 12