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
Note that if the --login-path switch is new to you, read my post about MySQL password-less command line scripts.