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 :
I restored the data successfully and made sure to configure the
FOREIGN_KEY_CHECKS again :
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 !