Get all the Foreign Key Relationships

An old asktom question was recently highlighted on Twitter: All Parent – Child tables in the database. I contributed a new solution, but here is one I think is more complete.

The Requirement: foreign key relationships

The questioner wanted to show the hierarchy of “parent-child” relationships, starting from the top ancestor and going down to the last descendant. He asked for the entire database.

By “parent-child” we mean foreign key relationships: the “parent” has a primary key constraint or a unique constraint, and the “child” has a foreign key constraint that references the parent key.

I assume I should omit any table that is neither parent nor child. I also add an IS_CYCLE column: if = 1, a CONNECT_BY_ISCYCLE condition was met. Finally, I show whether the referenced key is Primary or Unique.

Building the solution

Since we are working on the whole database, DBA_CONSTRAINTS is the appropriate source. For just my schema, I can use USER_CONSTRAINTS instead. For readability, I’ll restrict the output to the HR schema.

The basic information we need includes the owner, constraint name, constraint type and table name, together with the referenced constraint and its owner. I include only constraints of type ‘P’ (Primary), ‘U’ (Unique), and ‘R’ (foreign key Reference). In recent versions, I can restrict my query to owners that are not “Oracle maintained”.

with pur as (
  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME,
    R_OWNER, R_CONSTRAINT_NAME
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner in (select username from dba_users where oracle_maintained = 'N')
)
select * from pur;
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME R_OWNER R_CONSTRAINT_NAME
HR COUNTRY_C_ID_PK P COUNTRIES
HR COUNTR_REG_FK R COUNTRIES HR REG_ID_PK
HR DEPT_ID_PK P DEPARTMENTS
HR DEPT_LOC_FK R DEPARTMENTS HR LOC_ID_PK
HR DEPT_MGR_FK R DEPARTMENTS HR EMP_EMP_ID_PK
HR EMP_EMP_ID_PK P EMPLOYEES
HR EMP_DEPT_FK R EMPLOYEES HR DEPT_ID_PK
HR EMP_JOB_FK R EMPLOYEES HR JOB_ID_PK
HR EMP_MANAGER_FK R EMPLOYEES HR EMP_EMP_ID_PK
HR EMP_EMAIL_UK U EMPLOYEES
HR JOB_ID_PK P JOBS
HR JHIST_EMP_ID_ST_DATE_PK P JOB_HISTORY
HR JHIST_DEPT_FK R JOB_HISTORY HR DEPT_ID_PK
HR JHIST_EMP_FK R JOB_HISTORY HR EMP_EMP_ID_PK
HR JHIST_JOB_FK R JOB_HISTORY HR JOB_ID_PK
HR LOC_ID_PK P LOCATIONS
HR LOC_C_ID_FK R LOCATIONS HR COUNTRY_C_ID_PK
HR REG_ID_PK P REGIONS

 

Now we need to add the table the referenced constraints belong to. I took the easy path and did a self join.

with pur as (
  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME,
    R_OWNER, R_CONSTRAINT_NAME
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner in (select username from dba_users where oracle_maintained = 'N')
)
, relations as (
  select a.owner, a.table_name, a.r_owner,
    b.table_name r_table_name,
    b.constraint_type r_constraint_type
  from pur a join pur b
  on (a.R_OWNER, a.R_CONSTRAINT_NAME) = ((b.OWNER, b.CONSTRAINT_NAME))
  --and (a.OWNER, a.table_name) != ((b.OWNER, b.table_name))
)
select * from relations
OWNER TABLE_NAME R_OWNER R_TABLE_NAME R_CONSTRAINT_TYPE
HR COUNTRIES HR REGIONS P
HR DEPARTMENTS HR EMPLOYEES P
HR DEPARTMENTS HR LOCATIONS P
HR EMPLOYEES HR DEPARTMENTS P
HR EMPLOYEES HR EMPLOYEES P
HR EMPLOYEES HR JOBS P
HR JOB_HISTORY HR DEPARTMENTS P
HR JOB_HISTORY HR EMPLOYEES P
HR JOB_HISTORY HR JOBS P
HR LOCATIONS HR COUNTRIES P

 

Note that EMPLOYEES references itself.

UPDATE: I added a commented line at the end of RELATIONS. If you want to eliminate self-references, uncomment that line.

Now we need to identify the starting points for the hierarchical query. These are tables that are referenced, but do not reference any other table – though they can reference themselves. We’ll add those to the RELATIONS result set:

with pur as (
  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME,
    R_OWNER, R_CONSTRAINT_NAME
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner in (select username from dba_users where oracle_maintained = 'N')
)
, relations as (
  select a.owner, a.table_name, a.r_owner,
    b.table_name r_table_name,
    b.constraint_type r_constraint_type
  from pur a join pur b
  on (a.R_OWNER, a.R_CONSTRAINT_NAME) = ((b.OWNER, b.CONSTRAINT_NAME))
  -- and (a.OWNER, a.table_name) != ((b.OWNER, b.table_name))
)
, with_parents as (
  select * from relations
  union
  select r_owner, r_table_name, null, null, null
  from relations 
  where (r_owner, r_table_name) not in (
    select owner, table_name
    from relations
    where (owner, table_name) != ((r_owner, r_table_name))
  )
) select * from with_parents
OWNER TABLE_NAME R_OWNER R_TABLE_NAME R_CONSTRAINT_TYPE
HR COUNTRIES HR REGIONS P
HR DEPARTMENTS HR EMPLOYEES P
HR DEPARTMENTS HR LOCATIONS P
HR EMPLOYEES HR DEPARTMENTS P
HR EMPLOYEES HR EMPLOYEES P
HR EMPLOYEES HR JOBS P
HR JOBS
HR JOB_HISTORY HR DEPARTMENTS P
HR JOB_HISTORY HR EMPLOYEES P
HR JOB_HISTORY HR JOBS P
HR LOCATIONS HR COUNTRIES P
HR REGIONS

 

Finally, we can start from the rows where R_OWNER is null and go down the hierarchy:

with pur as (
  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME,
    R_OWNER, R_CONSTRAINT_NAME
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner in (select username from dba_users where oracle_maintained = 'N')
)
, relations as (
  select a.owner, a.table_name, a.r_owner,
    b.table_name r_table_name,
    b.constraint_type r_constraint_type
  from pur a join pur b
  on (a.R_OWNER, a.R_CONSTRAINT_NAME) = ((b.OWNER, b.CONSTRAINT_NAME))
  -- and (a.OWNER, a.table_name) != ((b.OWNER, b.table_name))
)
, with_parents as (
  select * from relations
  union
  select r_owner, r_table_name, null, null, null
  from relations 
  where (r_owner, r_table_name) not in (
    select owner, table_name
    from relations
    where (owner, table_name) != ((r_owner, r_table_name))
  )
)
select level LVL, OWNER, TABLE_NAME, R_OWNER, R_TABLE_NAME, R_CONSTRAINT_TYPE,
  connect_by_iscycle is_cycle
from with_parents
start with r_owner is null
connect by nocycle (r_owner, r_table_name) = ((prior owner, prior table_name))
order siblings by owner, table_name;
LVL OWNER TABLE_NAME R_OWNER R_TABLE_NAME R_CONSTRAINT_TYPE IS_CYCLE
1 HR JOBS 0
2 HR EMPLOYEES HR JOBS P 1
3 HR DEPARTMENTS HR EMPLOYEES P 1
4 HR JOB_HISTORY HR DEPARTMENTS P 0
3 HR JOB_HISTORY HR EMPLOYEES P 0
2 HR JOB_HISTORY HR JOBS P 0
1 HR REGIONS 0
2 HR COUNTRIES HR REGIONS P 0
3 HR LOCATIONS HR COUNTRIES P 0
4 HR DEPARTMENTS HR LOCATIONS P 0
5 HR EMPLOYEES HR DEPARTMENTS P 1
6 HR JOB_HISTORY HR EMPLOYEES P 0
5 HR JOB_HISTORY HR DEPARTMENTS P 0

 

Hope this helps…

Advertisements

9 thoughts on “Get all the Foreign Key Relationships

  1. Pingback: Foreign Key Relationships | master of disaster

    • Mayank,

      You mean because of the line
      and owner in (select username from dba_users where oracle_maintained = 'N')?
      I did mention that only works “in recent versions”, meaning 12.1, 12.2 and 18c.

      Just remove that line :-)

      Best regards, Stew

  2. Great post! Thanks! But I think, have you idea how find fastest way between two tables based on above solution?

  3. Hello Stew,

    Though not strictly related to the FK hierarchy topic,
    I wonder how exactly are the values of the IS_CYCLE columns assigned.

    Specifically, we see that in the first branch (starting with JOBS) both EMPLOYEES and DEPARTMENTS appear with IS_CYCLE = 1,
    while in the second branch (starting with REGIONS) only EMPLOYEES appears with IS_CYCLE = 1.

    As by the SQL documentation, which is the same in all versions, it seems to be clear enough when is a row considered
    to form a cycle, but less clear what Oracle will do when encountering such a row, besides avoiding to raise the cycle error.

    If the IS_CYCLE=1 were assigned “in the order of detection”, which is the ascending order of the LEVEL-s,
    I would have expected the see “more rows” marked with IS_CYCLE=1 at higher (or “later found”) LEVEL values,
    here levels 5 and 6 in the second branch vs levels 2 and 3 in the first branch.

    An academic question, of course, but, I think, interesting anyway.

    Thanks a lot & Best Regards,
    Iudith Mentzel

    • Hi Iudith,

      The results seem strange because EMPLOYEES has a self-reference. I added a line (commented out) that eliminates self-references. If you test with that line uncommented, you will see that IS_CYCLE = 1 only with EMPLOYEES and DEPARTMENTS because they reference each other.

      Best regards, Stew

  4. Hello Stew,

    Thanks a lot for this clarification :)
    I missed the point that the self-referencing table was still there in “relations”, being eliminated only in the “with_parents” second branch,
    so the hierarchy still reflects it as a cycle.

    Thinking of all the possible cases, cycles can also appear when the two tables involved do not even have adjacent levels,
    because of other table(s) appearing “between the two” in the hierarchy.

    Also, the cycles cause the same FK relation to appear several times in the output.
    For example, table JOB_HISTORY appears twice as referencing each of EMPLOYEES and DEPARTMENTS,
    so it appears 5 times in the hierarchy, while it only has 3 FK constraints.

    The reciprocal dependency between EMPLOYEES and DEPARTMENTS still appears in the hierarchy in both directions,
    marked with IS_CYCLE=1, while the self-referential FK of EMPLOYEES does NOT appear in the output,
    even if we do comment out the line that eliminates self-references.

    It looks like the behavior of the hierarchical nocycle query in the presence of two nodes referencing each-other is different
    from the behavior for a node that references itself.
    The display “stops earlier” for a node that references itself than for two nodes that reference each other.

    Cheers & Best Regards,
    Iudith

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

w

Connecting to %s