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.