MySQL table names ordered by dependency order

Question

I want to get table names ordered by it's dependency order.

For example: If I have table users, users_orders and orders I want to get table names this order: users (or orders does not matter), orders, users_orders.

If there is a way please help.

Answer 1

It sounds like you're trying to dump tables that have foreign key definitions, and you want to make sure the "parent" tables are dumped first so the "child" tables that reference them can be assured their foreign key definitions will work.

You can't do this in general, because cyclic dependencies are possible.

For example, if you have users and teams where each user has a reference to the team they belong to, but teams also has a reference captain to the specific user who is the team's captain, do you want to list users first, or teams first?

One alternative solution is to output all the tables in any order you want, but without foreign key definitions. After all the tables have been listed and their data too, then follow that with ALTER TABLE...ADD FOREIGN KEY commands.

Another alternative solution—the one used by mysqldump—is to SET FOREIGN_KEY_CHECKS=0 at the beginning. Then you can define the foreign key constraints without worrying about whether the referenced table has been created yet. The tables are dumped in alphabetical order.

But to answer your question more directly: you can use the INFORMATION_SCHEMA to discover which table dependencies exist.

SELECT table_schema, table_name, 
  GROUP_CONCAT(column_name ORDER BY ordinal_position) AS `columns`,
  MAX(referenced_table_schema) AS referenced_table_schema,
  MAX(referenced_table_name) AS referenced_table_name,
  GROUP_CONCAT(referenced_column_name ORDER BY ordinal_position) AS `ref_columns`
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE referenced_table_schema IS NOT NULL
GROUP BY table_schema, table_name;

MySQL doesn't support recursive queries until MySQL 8.0 (which is still under development). So you'll probably have to fetch the dependency data into your application and figure out the order you want to dump them.

But you still won't be able to handle cycles this way. You'll have to use one of the alternatives I described above.

Answer 2

You could do something like this.

1. Get a listing of all of the tables that don't have foreign key dependencies.

   SELECT TABLE_NAME
   FROM information_schema.tables
   WHERE table_schema='my_database'
     AND TABLE_NAME NOT IN (
     SELECT DISTINCT TABLE_NAME
     FROM information_schema.key_column_usage WHERE table_schema='my_database'
     AND referenced_table_name IS NOT NULL;
   

2. Get a listing of the tables that have foreign key dependencies on another table that does not have any forieng key dependencies.

   SELECT DISTINCT TABLE_NAME
   FROM information_schema.referential_constraints
   WHERE CONSTRAINT_SCHEMA='my_database'
     AND referenced_table_name NOT IN
       (SELECT DISTINCT TABLE_NAME
        FROM information_schema.table_constraints
        WHERE constraint_type = 'FOREIGN KEY')
     AND TABLE_NAME NOT IN
       (SELECT DISTINCT TABLE_NAME
        FROM information_schema.referential_constraints
        WHERE referenced_table_name IN
            (SELECT DISTINCT TABLE_NAME
             FROM information_schema.table_constraints
             WHERE constraint_type = 'FOREIGN KEY'));
   

3. Get a listing of the tables that have foreign key dependencies on other tables that themselves have foreign key dependencies.

   SELECT DISTINCT TABLE_NAME
   FROM information_schema.referential_constraints
   WHERE CONSTRAINT_SCHEMA='my_database'
     AND referenced_table_name IN
       (SELECT DISTINCT TABLE_NAME
        FROM information_schema.table_constraints
        WHERE constraint_type = 'FOREIGN KEY');
   

   Then you could use them in a script like the one below. It won't solve the circular dependency problem described above but it will create a MySQL dump file where the tables are ordered by foreign key dependencies.

   #!/usr/bin/env bash
   #
   # staged-mysqldump.sh
   #
   # Runs mysqldump against a database and splits the output into
   # three directories.
   #
   # <database_name>-STAGE-1 contains dumps of all of the tables
   # that don't have any foreign key constraints.
   # <database_name>-STAGE-2 contains dumps of all of the tables
   # that have only have foreign key constraints with other tables
   # that don't have foreign key constraints.
   # <database_name>-STAGE-3 contains dumps of the rest of the tables.
   #
   #
   DATABASE="$1"
   DUMPDIR="/var/tmp"
   MYSQL_CREDENTIALS="~/.my.cnf"
   DUMPDATE="$(date +%Y%m%d)"
   #
   # Write a statement that drops the database if it exists and
   # then write a create database statement to simulate a regular
   # mysqldump.
   #
   echo "DROP DATABASE IF EXISTS $DATABASE;" >> $DUMPDIR/$DATABASE-$DUMPDATE-dump.sql
   mysql --defaults-extra-file=$MYSQL_CREDENTIALS --skip-column-names --batch --execute "SHOW CREATE DATABASE $DATABASE" | sed "s/^$DATABASE\s\+//;s/$/;/" >> $DUMPDIR/$DATABASE-$DUMPDATE-dump.sql
   #
   # Dump the stage 1 tables.
   #
   printf "Dumping tables for %s - Stage 1\n" "$DATABASE"
   STAGE_1_TABLES=$(mysql --defaults-extra-file=$MYSQL_CREDENTIALS --skip-column-names --batch --execute "SELECT table_name FROM information_schema.tables WHERE table_schema='$DATABASE' AND table_name NOT IN (SELECT distinct table_name FROM information_schema.key_column_usage WHERE table_schema='$DATABASE' AND referenced_table_name IS NOT NULL)")
   printf "Stage 1 Start Time: %(%Y-%m-%d - %H:%M:%S)T\n"
   mysqldump --defaults-extra-file=$MYSQL_CREDENTIALS --databases $DATABASE --tables $STAGE_1_TABLES >> $DUMPDIR/$DATABASE-$DUMPDATE-dump.sql
   printf "Finished dumping tables for %s - Stage 1\n" "$DATABASE"
   printf "Stage 1 End Time: %(%Y-%m-%d - %H:%M:%S)T\n"
   #
   # Dump the stage 2 tables.
   #
   printf "Dumping tables for %s - Stage 2\n" "$DATABASE"
   STAGE_2_TABLES=$(mysql --defaults-extra-file=$MYSQL_CREDENTIALS --skip-column-names --batch --execute "SELECT DISTINCT table_name FROM information_schema.referential_constraints WHERE constraint_schema='$DATABASE' AND referenced_table_name NOT IN (SELECT DISTINCT table_name FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY') AND table_name NOT IN (SELECT DISTINCT table_name FROM information_schema.referential_constraints WHERE referenced_table_name IN (SELECT DISTINCT table_name FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY'))")
   printf "Stage 2 Start Time: %(%Y-%m-%d - %H:%M:%S)T\n"
   mysqldump --defaults-extra-file=$MYSQL_CREDENTIALS --databases $DATABASE --tables $STAGE_2_TABLES >> $DUMPDIR/$DATABASE-$DUMPDATE-dump.sql
   printf "Finished dumping tables for %s - Stage 2\n" "$DATABASE"
   printf "Stage 2 End Time: %(%Y-%m-%d - %H:%M:%S)T\n"
   #
   # Dump the stage 3 tables.
   #
   printf "Dumping tables for %s - Stage 3\n" "$DATABASE"
   STAGE_3_TABLES=$(mysql --defaults-extra-file=$MYSQL_CREDENTIALS --skip-column-names --batch --execute "SELECT DISTINCT table_name FROM information_schema.referential_constraints WHERE constraint_schema='$DATABASE' AND referenced_table_name IN (SELECT DISTINCT table_name FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY')")
   printf "Stage 3 Start Time: %(%Y-%m-%d - %H:%M:%S)T\n"
   mysqldump --defaults-extra-file=$MYSQL_CREDENTIALS --databases $DATABASE --tables $STAGE_2_TABLES >> $DUMPDIR/$DATABASE-$DUMPDATE-dump.sql
   printf "Finished dumping tables for %s - Stage 3\n" "$DATABASE"
   printf "Stage 3 End Time: %(%Y-%m-%d - %H:%M:%S)T\n"
   

If you use this you'll probably want to adjust the options you're passing to MySQL.