Disabling foreign key constraints in MySQL

I have recently made a backup of our Laravel Vapor MySQL database before a temporary deployment on our staging environment.

When I wanted to restore the MySQL database using (https://dbeaver.io/)[DBeaver], an open source GUI utility, I encountered an error because tables were referenced by foreign key constraints :

ERROR 3730 (HY000) at line 104: Cannot drop table 'articles' referenced by a foreign key constraint 'publications_article_id_foreign' on table 'publications'.

Foreign Keys offer you the option to have updates and deletes cascade from the parent (reference) table to the child table. I could have manually dropped every table in our database prior to restoring the data, but I instead decided to follow this simple trick :

Before restoring the data, I executed the following MySQL command :

SET FOREIGN_KEY_CHECKS=0;

I restored the data successfully and made sure to configure the FOREIGN_KEY_CHECKS again :

SET FOREIGN_KEY_CHECKS=1;

You can also add those commands respectively at the top and bottom of your .sql backup file.

Another way to bypass foreign constraints on database restore is to use DISABLE KEYS :

ALTER TABLE table_name DISABLE KEYS;

You can now restore the database.

You should reconfigure ENABLE KEYS after restoring the database :

ALTER TABLE table_name ENABLE KEYS;

Please note that DISABLE KEYS only works for MyISAM and not InnoDb storage engine.

Let me know if this article was helpful to you !

Comments

[Michael Corcelli]

Please send me an email to [e-mail]. I have been trying to get you but the message is getting rejected. Thanks