Building a Table Dependency Graph With A Recursive Query

Question

I am trying to build a dependency graph of tables based on the foreign keys between them. This graph needs to start with an arbitrary table name as its root. I could, given a table name look up the tables that reference it using the all_constraints view, then look up the tables that reference them, and so on, but this would be horrible inefficient. I wrote a recursive query that does this for all tables, but when I add:

START WITH Table_Name=:tablename

It doesn't return the entire tree.

Solution 1

    select parent, child, level from (
select parent_table.table_name parent, child_table.table_name child
 from user_tables      parent_table,
      user_constraints parent_constraint,
      user_constraints child_constraint,
      user_tables      child_table
where parent_table.table_name = parent_constraint.table_name
  and parent_constraint.constraint_type IN( 'P', 'U' )
  and child_constraint.r_constraint_name = parent_constraint.constraint_name
  and child_constraint.constraint_type   = 'R'
  and child_table.table_name = child_constraint.table_name
  and child_table.table_name != parent_table.table_name
)
start with parent = 'DEPT'
connect by prior child = parent

should work (replace the table name, of course) assuming that everything is in the same schema. Use the DBA_ versions of the data dictionary tables and conditions for the OWNER and R_OWNER columns if you need to handle cross-schema dependencies. On further reflection, this does not account for self-referential constraints (i.e. a constraint on the EMP table that the MGR column references the EMPNO column) either, so you'd have to modify the code to handle that case if you need to deal with self-referential constraints.

For testing purposes, I added a few new tables to the SCOTT schema that also reference the DEPT table (including a grandchild dependency)

SQL> create table dept_child2 (
  2  deptno number references dept( deptno )
  3  );

Table created.

SQL> create table dept_child3 (
  2    dept_child3_no number primary key,
  3    deptno number references dept( deptno )
  4  );

Table created.

SQL> create table dept_grandchild (
  2    dept_child3_no number references dept_child3( dept_child3_no )
  3  );

Table created.

and verified that the query returned the expected output

SQL> ed
Wrote file afiedt.buf

  1  select parent, child, level from (
  2  select parent_table.table_name parent, child_table.table_name child
  3   from user_tables      parent_table,
  4        user_constraints parent_constraint,
  5        user_constraints child_constraint,
  6        user_tables      child_table
  7  where parent_table.table_name = parent_constraint.table_name
  8    and parent_constraint.constraint_type IN( 'P', 'U' )
  9    and child_constraint.r_constraint_name = parent_constraint.constraint_name
 10    and child_constraint.constraint_type   = 'R'
 11    and child_table.table_name = child_constraint.table_name
 12    and child_table.table_name != parent_table.table_name
 13  )
 14  start with parent = 'DEPT'
 15* connect by prior child = parent
SQL> /

PARENT                         CHILD                               LEVEL
------------------------------ ------------------------------ ----------
DEPT                           DEPT_CHILD3                             1
DEPT_CHILD3                    DEPT_GRANDCHILD                         2
DEPT                           DEPT_CHILD2                             1
DEPT                           EMP                                     1

Solution 2

Simplest way to do this is to copy all the FK info into a simple, 2-column (parent,child) table, and then use the following algorithm:

while (rows left in that table)
  list = rows where table name exists in child but not in parent
  print list
  remove list from rows

that's all. Basically, you first print and remove all the nodes that don't depend on anything. After that being done, some other nodes will get free and you can repeat process.

P.S. Make sure you don't insert self-referencing tables in the initial list (child=parent)

Comments

[Tulains Córdova]

When I run that query I get ORA-01437: cannot have join with CONNECT BY

[Justin Cave]

@user1598390 - Are you saying that when you run the exact test case I posted that you get an error? Or are you doing something (even slightly) different? Without seeing your code, it is highly unlikely that we're going to be able to do much to help you. You're probably going to need to create a new question where you can post the exact query you're using and, ideally, show it failing with some sample tables that you post.

[Tulains Córdova]

I copy your SQL, paste it into an Oracle client ( PL/SQL Developer ), then hit F8 to run it, and get the error.

[Justin Cave]

@user1598390 - OK, so you're using the default SCOTT schema, you created the tables I created here, and running exactly the same SQL statement I posted to get the dependencies of the DEPT table? As you can see, it worked for me. What version of Oracle are you using?

[Tulains Córdova]

No, I'm using a real production schema with lots of tables and dependencies. Oracle version is 8.1.7.

[Justin Cave]

@user1598390 - Does the simple example I posted work for you? 8.1.7 has been depricated for, what, a decade? I haven't had access to an 8.1.7 database in many years. So I wouldn't be shocked if an ancient version of Oracle had a more limited support for the connect by clause.