Looking for a short way to drop all tables of a MySQL or MariaDB database? This is a quick tutorial showing how to delete all tables of a MySQL / MariaDB database instantly bypassing the foreign key constraints in a couple of easy steps in Navicat.
You can watch the video tutorial above or view it on YouTube, or just follow the textual instructions with screenshots below:
When it comes to deleting relational database tables, some might have gotten tangled in the web of foreign key relationships and resist to drop commands, producing errors like “MySQL error 1217 – Cannot delete or update a parent row: a foreign key constraint fails”.
What if you were to drop about 150 stubborn tables with foreign key references among each other?
Here’s a 3-step process which will solve this issue. Yet you can apply it once and re-use it later with only a single-click call with a tool like Navicat minimizing it to a single step for the future:
Open a new query tab and type – or just copy and paste the following as shown in the screenshot a little below:
SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;') FROM information_schema.tables WHERE table_schema = 'your_database';
Remember to substitute ‘your_database’ with the name of your own schema to run this on.
Copy all the results of the query by clicking the gutter (at the beginning) of any line once, next by selecting all the results (e.g. Edit > Select All), and then by copying all of the selected results (hint: right-clicked drop-down menu works!)
Open a new query tab, and paste what you have copied from the previous query.
Open a new line at the top of the drop statements and insert the following one line code so as to make it the first command of all statements:
SET FOREIGN_KEY_CHECKS = 0;
Go to the end of the last statement, and type or copy the following:
SET FOREIGN_KEY_CHECKS = 1;
You can now execute the query, and the job gets done.
Finally you can save the queries in Navicat and use them again later as needed.