Pragmatism in the real world

Turn off foreign key checks when restoring a mysql file dump

I recently received a MySQL dump file where the various tables in it had foreign keys to each other.

I usually restore with this command:

mysql --login-path=rob < dump.sql

but this generated the error:

ERROR 1217 (23000) at line 288805: Cannot delete or update a parent row: a foreign key constraint fails

It turns out that the easiest way to solve this is to use the --init-command switch to set foreign keys off for this session only:

mysql --login-path=rob --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" < dump.sql

Problem solved!

Note that if the --login-path switch is new to you, read my post about MySQL password-less command line scripts.