Map dependency between tables

[former_SQLA_member1694872]

Hello, how do I map dependencies between tables? (I'm using ASA 9) Example: I have to send the order table before the order item tables. I would like to do a select where it can return me in order of dependency between tables.

select * ???
from systable
left outer join SYSFOREIGNKEY on
(SYSFOREIGNKEY.PRIMARY_TABLE_ID = SYSTABLE.TABLE_ID) 
WHERE TABLE_TYPE = 'BASE'

Thank you!

[VolkerBarth]

Do you just want to list the table names in “FK order“, or do you need more columns in the select list, such as relevant columns, role name etc.? And what about self-referencing tables or FK cycles - do those appear?

[former_SQLA_member1694872]

I need only the name of the tables in "FK order". And the self-referral tables is not necessary.

[VolkerBarth]

Here's an attempt to do so via a recursive union. This should run with v9 and newer versions as I have used the "compatibility system views" SYSTABLE and SYSFOREIGNKEY. I vave tested with 9.0.2.3508 and 12.0.1.4403.

The basic idea is to list all tables that are not included as FKs in FK relationships, and then to recursively join with all tables who have FK relationships to the former.

In the sample, I have restricted to use tables created by creator 1 - you should therefore adapt to your schema, say be filtering for a different owner id, by restricting to particular table names or by excluding system tables, whatever fits.

-- recursive CTE to collect the relevant FK information
with recursive pk_table (pk_table_id, pk_table_name, fk_table_id, fk_table_name, role, level)
as
(
  -- initial subquery: select all tables who are not children (FKs) in a FK relationship, choose NULL as their "parent" and role and 0 as level
  (select cast(null as unsigned int) as pk_table_id, cast(null as sysname) as pk_table_name, table_id as fk_table_id, table_name as fk_table_name, cast(null as sysname) as role, cast(0 as int) as level
   from systable
   where creator = 1 and table_id not in (select foreign_table_id from sysforeignkey))
   union all
  -- recursive subquery: join with all their direct childen and increase the level
   select pkt.fk_table_id, pkt.fk_table_name, sft.table_id as fk_table_id, sft.table_name as fk_table_name, sfk.role, pkt.level + 1
   from pk_table pkt 
      inner join sysforeignkey sfk on pkt.fk_table_id = sfk.primary_table_id
         inner join systable sft on sfk.foreign_table_id = sft.table_id and sft.creator = 1
   where sfk.foreign_table_id <> sfk.primary_table_id)  -- exclude self-references

/*-- test main query (remove comment to test): list all tables and FK relationships
select distinct * from  pk_table
where level < 20 -- recursive 
order by level, fk_table_name, pk_table_name, role;*/
-- main query: list tables ordered by their maximum level (= highest degree of FK relationships)
select fk_table_id, fk_table_name, max(level) as maxLevel
from pk_table
group by fk_table_id, fk_table_name
order by maxLevel, 2;

Used on a v9 demo database the (currently deactivated) test query returns:

pk_table_id,pk_table_name,fk_table_id,fk_table_name,role,level
NULL,NULL,438,'contact',NULL,0
NULL,NULL,439,'customer',NULL,0
NULL,NULL,440,'fin_code',NULL,0
NULL,NULL,442,'product',NULL,0
440,'fin_code',441,'fin_data','ky_code_data',1
439,'customer',436,'sales_order','ky_so_customer',1
440,'fin_code',436,'sales_order','ky_so_fincode',1
442,'product',437,'sales_order_items','ky_prod_id',1
436,'sales_order',437,'sales_order_items','id_fk',2

and the group query returns:

fk_table_id,fk_table_name,maxLevel
438,'contact',0
439,'customer',0
440,'fin_code',0
442,'product',0
441,'fin_data',1
436,'sales_order',1
437,'sales_order_items',2

Important note:

As you have requested, cyclic relationships do not appear in your schema. If they do appear, the according tables are ignored from the above queries if they are not involved as children of other tables. In the sample database, tables "department" and "employee" have cyclic relationships and are therefore ignored here. (The query could be enhanced to list those, too, but I'm not sure about their "positioning" - which one should come first?)