Are you getting foreign key constraint errors when trying to restore Craft CMS database backups? Here is how to solve it.
Every time I download a backup of a Craft 3 database and try to restore it to my local development environment, I get this error:
Cannot add foreign key constraint
This happens because the SQL dump has the data in a different order than it should be loaded in according to the foreign key constraints.
The solution is to use a MySQL session variable to turn off foreign key constraints and be able to load your data in the order it is currently in.
How?
WARNING: Do NOT even think of doing this in your production environment. Only development. You’ve been warned.
Open your .sql dump in your favorite text editor and put this line at the top:
SET FOREIGN_KEY_CHECKS = 0;
Save it, then retry your import. Works for me every time.
I wrote this post so that I don’t have to search how to do it in six months when I need to do it again.
p.s. if you want to do something similar in postgres instead of mysql, search for SET CONSTRAINTS ALL DEFERRED.
