How do I get a list of all tables in a mysql db, ordered by foreign key dependencies?

Question

I want a list of tables, ordered by foreign key. So if table "Book" has a FK dependency on table "Author", Author would be listed first and Book second. I have no cyclic dependency loops but I do have a few self-join relationships (table has an FK dependency on itself). I would like a script to generate this list. (Or perhaps a recursive CTE in mysql 8?)

I can get the foreign key dependencies from: (mysql query)

select CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
from information_schema.referential_constraints
where constraint_schema = 'DBName'
order by TABLE_NAME;

And the table-list from:

show full tables where table_type <> "VIEW";

Background: I would like to copy my mysql db-schema into an in-memory H2 database for testing. But H2 does not support full mysql syntax. In particular FOREIGN_KEY_CHECKS=0 is not supported and H2 SET REFERENTIAL_INTEGRITY FALSE only applies to insert/update-constraint-checks not table-creation. So my tables need to be created in the correct order, according to FK dependencies.

I can dump the schema with mysqldump. But mysqldump does not order the table creation by foreign keys. If table "Child" has a FK dependency on table "Parent", mysqldump appears to put the table-creation in alphabetical order so Child will be created before Parent even though it depends on Parent. It relies on disabling foreign key checks while all the tables are created.

So if I had a list of table names, in fk order, I could pass that to mysqldump to dump the schema.

Answer 1

This should get you started:

I created a set of tables in a hierarchy, so t1 is parent of t2 and t3, t2 is parent of t4 and t5, t3 is parent of t6, t6 is parent of t7.

with recursive cte as (
 select t.table_name, 0 as depth 
 from information_schema.tables t
 left join information_schema.referential_constraints r 
  on t.table_name =  r.table_name
 where t.table_schema = 'test' and r.table_name is null
 union all
 select r.table_name, cte.depth+1 
 from cte join information_schema.referential_constraints r 
  on cte.table_name = r.referenced_table_name
) select * from cte order by depth asc;

+------------+-------+
| TABLE_NAME | depth |
+------------+-------+
| t1         |     0 |
| t2         |     1 |
| t3         |     1 |
| t4         |     2 |
| t5         |     2 |
| t6         |     2 |
| t7         |     3 |
+------------+-------+

I didn't test for any self-referential tables. Maybe you need a condition in the CTE to exclude cases where r.table_name=cte.table_name. I'll leave that to you.

Answer 2

My original python script solution: (semi-manual approach to allow custom order)

find-tables-order-by-referential-constraints.py

from collections import defaultdict

# show full tables where table_type <> "VIEW";
tables = [
  "Book",
  "Author",
  "Illustrator",
  "Gender",
  "PublicationArticle",
  "OrphanTable",
  "OrphanTableDependency"
]

# FK-dependencies from:
#  (used sublime multiple-cursors to reformat all rows at the same time)
# select CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
# from information_schema.referential_constraints
# where constraint_schema = 'DBName'
# order by TABLE_NAME;
dependencies = defaultdict(list)
dependencies["Book"].append("Author")
dependencies["Book"].append("Illustrator")
dependencies["Author"].append("Gender")
dependencies["PublicationArticle"].append("Author")
dependencies["OrphanTable"].append("OrphanTableDependency")

reversedeps = defaultdict(list)
for k, v in dependencies.items():
  for reftable in v:
    reversedeps[reftable].append(k)

result = []

def add_dependencies_then_you(table):
  if table in result:
    return
  for reftable in dependencies[table]:
    if reftable != table and reftable not in result:
      add_dependencies_then_you(reftable)
  print(table)
  result.append(table)

# add dependency-root table(s)
add_dependencies_then_you("Book")

# optional: custom order chosen to make more sense than random
# add_dependencies_then_you("PreferredNextTable1")
# add_dependencies_then_you("PreferredNextTable2")

# if any results have other tables depending on them, add those next
while(True):
  print
  result_count = len(result)
  for completed_table in result:
    for refs_me_table in reversedeps[completed_table]:
      add_dependencies_then_you(refs_me_table)
  if(result_count == len(result)):
    break;

# add any missing tables not hit above
for table in list(tables):
  if table not in result:
    add_dependencies_then_you(table)