DEV Community

loading...
Cover image for Disabling foreign key constraints in MySQL

Disabling foreign key constraints in MySQL

wlarch profile image William L'Archeveque ・1 min read

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'.
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

SET FOREIGN_KEY_CHECKS=1;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

You can now restore the database.

You should reconfigure ENABLE KEYS after restoring the database :

ALTER TABLE table_name ENABLE KEYS;
Enter fullscreen mode Exit fullscreen mode

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

Let me know if this article was helpful to you !

Discussion (0)

pic
Editor guide