Techie Corner

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:-

• 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

Tags: foreign key constraint, import db foreign key constraint error, import foreign key constraint error, import innodb foreign key constraint error, import mysql foreign key constraint, import mysql innodb, innodb foreign key constraint, mysql, mysql foreign key constraint, mysql foreign key constraint error

---