Thursday, May 7, 2015

Disabling foreign key checks on the linux command line

I've used these two commands a lot of to turn off foreign key checking and to turn it back during a migration:

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

However, today I was importing some data for a script I'm writing and I needed to disable constraints before running the import. I was having trouble getting it to work but conveniently, I found the answer on stackoverflow...

http://stackoverflow.com/questions/15938786/disabling-foreign-key-checks-on-the-command-line

QUESTION:

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.
ANSWER:

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

No comments:

Post a Comment