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 import contacts from SIM card to nokia N900
How to extract .deb file in Linux / Mac
WordPress Custom Taxonomy Pagination show 404 page not found error
How to setup multiple identities in Thunderbird?
jQuery: disable autoscrolling to top when click on anchor
How to auto compact Email folder for Thunderbird Email Client
How to check Zimbra Version without ssh access?
Virtue Theme: How to enable slider in shop page?
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!