Foreign keys between two tables

In a comment on my post about getting all the foreign key relationships, I was asked how to get the shortest path between two tables. Not too easy…

UPDATE: you can see how hard it is by the fact I have had to correct two bugs, one concerning direct or indirect self-references.

Using the ALL_CONSTRAINTS view, we can put together all the foreign key relationships between child tables and parent tables, and we can do hierarchical queries to go up and down branches of the hierarchy. If you give me two tables, though, how do I know whether to go up or down, and which table to start with?

The idea

I decided to start with both tables and go up. I expect children to outnumber parents, so going up should be more efficient. I start with both tables, knowing that at most one will be the descendant of the other. I then go up the hierarchy until I either get to the top or hit one of the tables. The “shortest path” is the one where the final LEVEL is smallest.

The code

I start with a little subquery that provides the target tables.

with search_for (owner, table_name) as (
  select 'OBE', 'OEHR_COUNTRIES' from dual union all
  select 'OBE', 'OEHR_ORDER_ITEMS' from dual

Then I get all the constraints that may be involved in foreign key relationships.

, pur as (
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner in (select username from dba_users where oracle_maintained = 'N')
) and owner in (select username from dba_users where oracle_maintained = 'N')

I do a self join on the PUR subquery to add the parent table to each row.
UPDATE: added a line to RELATIONS to avoid self-references.

, relations as (
  select a.owner, a.table_name,
    a.r_owner, b.table_name r_table_name
  from pur a join pur b
  and (a.OWNER, a.table_name) != ((b.OWNER, b.table_name))

Now I start going up the hierarchy:

  • I “start with” both tables
  • I “connect by” going up the hierarchy, stopping before the other table can be a child
    This is important, because the other table could be at the top of the hierarchy and not be a child at all.
  • The WHERE clause is applied after the CONNECT BY: I only keep rows where I found the other table.
  • I now have one row for each path from one table to the other. I use SYS_CONNECT_BY_PATH to concatenate the original child and all the parents going up the hierarchy.
    Notice I have to use one literal (<) within the first parameter and another (/) as the second parameter. I wanted to use ‘<‘ both places, but Oracle won’t let me.
  • Finally, I choose the shortest path, translate ‘/’ to ‘<‘, get rid of the first ‘<‘ and display the result.


  • To avoid indirect self-referencing, I make sure the last remote table is not the same and the original table!
  • I had the ‘<‘ outside of the CASE statement. Now it is inside so you do not get ‘<<‘ anymore.
, paths as (
  select row_number() over( order by
    case when (r_owner, r_table_name) != ((connect_by_root(owner), connect_by_root(table_name)))
      then level end
    ) rn, 
      case level when 1 then owner||'.'||table_name || '<' end ||
    '/') path
  from relations r
  where (r_owner, r_table_name) in (select * from search_for)
  start with (owner, table_name) in (select * from search_for)
  connect by nocycle (owner, table_name) = ((prior r_owner, prior r_table_name))
  and (owner, table_name) not in (select * from search_for)
select substr(translate(path,'/','<'), 2) best_path
from paths where rn = 1;


As the man said, hope this helps…


2 thoughts on “Foreign keys between two tables

  1. Fantastic work. Thanks for your reply. One more update could you add (if you want of course). I mean referenced column.
    Anyway thank you for you post, it is really helpful

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s