Magento: Fixing Cannot add or update a child row: a foreign key constraint fails

I’m currently completing a rather large project for a client. While I regularly make backups before installing any major extensions or conducting core work, yesterday I needed to make my first restore on this particular database.

While Magento has a great tool for backups and restores, I always prefer to do it manually from either SSH or in this case, PHPMyAdmin.

I dropped all the tables in the database in order to import a fresh backup from an .sql file. Import was a little too quick than expected and after 5 minutes, looked to be complete at first glance, but it also resulted in an error:

While the database tables were all there, I checked the site front-end and the design had reverted back to the default template. Tried to log in but no username existed. Checking the database for admin_user, none existed. Hmmm. Seemed the database tables were present, but the data was in fact, empty.

This occurs as the Magento database makes use of foreign key constraints to ensure database integrity. As an example, if a category is deleted, all categories below it in the category tree must be deleted too.

If you make a backup of your Magento database using the built-in backup function (Admin > System > Tools > Backup), Magento automatically inserts special statements in the .sql file to avoid foreign key checks when the tables are eventually restored.

If you backup your Magento database using other tools, like phpMyAdmin, these special statements will be missing.

The error then occurs because the data you are importing is provided table by table, row by row, without regard to the logical structure and integrity of the database.

So, to restore a .sql file backup without constraint checking, I added the following statements at the beginning of the .sql file:

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

At the end of the file, add the statements required to turn constraint checking ON again:

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;

With these little snippets at either ends of the sql file, I was able to restore the database fully from a .sql file, all 110mb of it and after 15-20 minutes of importing, fully restored the database backup.