Search code examples
mysqlcommand-lineforeign-keysmysqlimport

Disabling foreign key checks on the command line

I have a backup script for my MySQL database, using mysqldump with the --tab option so it produces a .sql file for the structure and a .txt file (pipe-separated) for the content.

Some tables have foreign keys, so when I import it I'm getting the error:

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

I know about using SET FOREIGN_KEY_CHECKS=0 (and SET FOREIGN_KEY_CHECKS=1 afterward). If I add those to each .sql file then the import works. But then obviously on the next mysqldump those get overwritten.

I also tried running it as a separate command, like below but the error comes back:

echo "SET FOREIGN_KEY_CHECKS=0" | mysql [user/pass/database] 
[all the imports]
echo "SET FOREIGN_KEY_CHECKS=1" | mysql [user/pass/database]

Is there some other way to disable FK checks on the command line?

Solution

  • You can do this by concatenating the string to the file inline. I'm sure there's an easier way to concatenate strings and files, but it works.

    cat <(echo "SET FOREIGN_KEY_CHECKS=0;") imports.sql | mysql

    I don't think you need to set it back to 1 since it's just one session.