SQL Query While uploading sql file

Question

I was uploading localhost site to live server. During the process I was importing .sql file from localhost to PHPMyAdmin live server database. After the uploading process was complete I got an error of:

SQL query:

ALTER TABLE `core_email_queue_recipients`
ADD CONSTRAINT `FK_6F4948F3ABF97DE12127EF14B140802A`
FOREIGN KEY (`message_id`) REFERENCES `core_email_queue` (`message_id`)
ON DELETE CASCADE ON UPDATE CASCADE

MySQL said:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`buddydea_magento`.`#sql-2aae_84a13f`, CONSTRAINT `FK_6F4948F3ABF97DE12127EF14B140802A` FOREIGN KEY (`message_id`) REFERENCES `core_email_queue` (`message_id`) ON DELETE CASCADE ON UPDATE CASC)

I have looked for the solution but didn't find the right answer.

Please help me to remove this error.

Answer 1

Excute below query before upload

SET FOREIGN_KEY_CHECKS=0;

Once uploaded revert back

SET FOREIGN_KEY_CHECKS=1;

Answer 2

I have found that if I export from phpmysql, even with "disable foreign key checks" set both at export and import, my import has been failing. Phpmysql is exporting in an order that makes no sense, creating the keys too early, or not destroying them when dropping the tables, and not ignoring the checks totally.

BUT THERE IS A WAY - if I export the database from within Magento's Backup tool - that file imports in phpmyadmin without errors.

Wish I'd tried that earlier