Knowledge Base

Unable to import a database dump: Cannot add or update a child row: a foreign key constraint fails

Symptoms

When importing a database dump at Domains > example.com > Databases > Import Dump, the operation fails with the following error message in Plesk:

Unable to import the example_com dump:
ERROR 1452 (23000) at line 4907226: Cannot add or update a child row: a foreign key constraint fails (`example`.<result 2 when explaining filename '#sql-852_12b68d'>, CONSTRAINT `FK_attribute` FOREIGN KEY (`entity_type_id`) REFERENCES `entity_type` (`entity_type_id`) ON DELETE CASC)

Cause

Incorrect database design.

Resolution

Apply one of the following solutions.

Disabling foreign key check in the database dump file (any MySQL version)

Open a database dump in a text editor.
Add the line below at the beginning of the file:

SET FOREIGN_KEY_CHECKS=0;
Go back to Plesk and restore this dump at Domains > example.com > Databases > Import Dump.

Disabling foreign key check in MySQL (MySQL versions 5.5 and greater)

Connect to the Plesk server via SSH.
Start MySQL:

# plesk db
Temporary disable foreign key checks in MySQL with the command:

mysql> SET GLOBAL FOREIGN_KEY_CHECKS=0;
Go back to Plesk and restore the database dump at Domains > example.com > Databases > Import Dump.
Enable foreign key checks in MySQL back:

mysql> SET GLOBAL FOREIGN_KEY_CHECKS=1;
Exit MySQL:

mysql> exit