Tried to import a mysql innodb from a sql dump file, but it keep show me there is an foreign key constraint error. Due to innodb has foreign key check validation, it does not allow me to continue n show me foreign key constraint error. Ya! I know my data having some foreign key constraint problem, but what i need to do now is to import my sql dump file!
Here’s the error message “#1452 – Cannot add or update a child row: a foreign key constraint fails…”
After going thru the mysql documentation, finally i found the solution! This solution not only allow me to import my innodb, but also improve the import speed tremendously!
To import mysql innodb with foreign key constraint error, follow the steps below:-
Advertisements
- If you have the access to mysql thru terminal, then you can use the command below:-
mysql> SET foreign_key_checks = 0; mysql> SOURCE your_db_dump_file; mysql> SET foreign_key_checks = 1;
- If you import your database thru web interface like phpmyadmin, then you need to edit the sql dump file. Add this to the 1st line of your sql db dump file:-
SET foreign_key_checks = 0;
- then add this line to the last line in your sql db dump file:-
SET foreign_key_checks = 1;
- Now you may import your sql dump file, and it should have no error by now.
Reference: MySQL Doc
Related posts:
How to create mailing list in Thunderbird
Open Office: How to change page layout to landscape
How to add native menu support in your WordPress theme?
MySQL: How to add column to existing table
How to add calendar in Thunderbird - Lightning Calendar add-on
Virtue Theme: How to enable slider in shop page?
Prestashop 1.6: How to show price in catalog mode?
How to enable personal file sharing in Ubuntu 10.04
Share this with your friends:-
thank you it, finally a solution that works 🙂
Great solution.
Thanks a lot.
Thank you, saved my life!
thanks !
Thanks!
It’s work fine!