Order table names by foreign key constraint (i.e. table x record needs to exist before table y record can be created)

Question

Is there any way to query the data dictionary to get a list of tables in the order in which they should be populated.

I have a project where I am doing bulk inserts on a large number of tables and it would be hugely helpful if I could get a list of the tables that was sorted by the order in which they should be populated.

My specific use case is MySql but it would be interesting to know if this capability also existed in Oracle, MS SqlServer etc.

EDIT: For example, if I create these tables (psudo code)

person_type (
  person_type_id
);

person (
  person_id,
  person_type_id references person_type.person_type_id
);

address (
  address_id,
  street1,
  street2,
  city,
  state,
  zip
);

person_address (
  person_id references person_id
  address_id references address.address_id
);

I would expect the sort order to be something like this:

0 address

0 person_type

1 person

2 person_address

address has no dependencies so can be populated first (sort rank = 0)
person_type has no dependencies so can also be populated first (sort rank = 0)
person depends on person_type so it cannot be populated until an appropriate person_type record is created (sort rank = 1)
person_address depends on person_type, person, and address so it can only be populated after records exist in those tables (sort rank = 2)

So you want something to dig on the db schema and give you and idea of how the relatioships depth are for each table? — Juan Carlos Oropeza, Jan 15 '16 at 19:09
maybe this can help http://stackoverflow.com/questions/806989/mysql-how-to-i-find-all-tables-that-have-foreign-keys-that-reference-particular — Juan Carlos Oropeza, Jan 15 '16 at 19:14

John · Answer 1 · 2016-01-16T14:04:05.447

I ended up doing a dump of the database that included only the structure and not the data. The tables will need to be created in the reverse order of what I'm looking for (i.e. before a table can create a foreign key to another table that other table must be created first) therefore the create table statements will be in the reverse order I want. I then ran this grep to get the create statements in the order I wanted.

tac dump.sql | grep -i "create table" dump.sql | tac > tableNames2.txt

A few global search and replaces later I had exactly what I was looking for.

I also turned off the foreign key constraints during the import and turned them back on at the end of the import with this:

set foreign_key_checks = 0
set foreign_key_checks = 1

Order table names by foreign key constraint (i.e. table x record needs to exist before table y record can be created)

1 Answers1