How to restore MySQL Database with Foreign Keys

Hello everyone, yesterday while migrating one database from one server to another I went into this situation, when I failed to restore MySQL database with foreign keys due to the following error:

ERROR 1215 (HY000) at line 368: Cannot add foreign key constraint

 

restore mysql database with foreign keys

Why we get this error while restoring database with Foreign Keys?

This generally happen when we restore a database and the table it restores contains a foreign key to another table isn’t created yet. Sounds hard? let’s understand with example:

Let’s suppose we have a database D1 containing 2 tables: T1 and T2,

T1 has a foreign key in T2.

Now while restoring it first T1 table restored but the column which is in T2 is not there as T2 has not been restored yet, this results into our main error.

 

How to resolve this Error?

We can simply resolve this error in simple steps:

Take backup normally:

mysqldump -u user -p database_name > dbdump.sql

 

If database already exist use:

SET FOREIGN_KEY_CHECKS=0;

SOURCE /pathToFile/dbdump.sql;

SET FOREIGN_KEY_CHECKS=1;

 

Or we can edit the dump file (if file size is less) and add

SET FOREIGN_KEY_CHECKS=0;   (at the top of file)

SET FOREIGN_KEY_CHECKS=1;   (at the end of file)

 

If database is not present simply create the database first and then restore it using above steps.

Now you should have been successful in restoring MySQL database with foreign keys constraint. Comment if you still face any problem.

 

 

Leave a Comment