MySQL drop table and foreign keys tip - the FOREIGN_KEY_CHECKS variable

MySQL “DROP TABLE” FAQ: Help, my MySQL database tables have a lot of foreign keys, and as a result it's a pain to use the MySQL DROP TABLE command in my scripts; they keep failing because of all the foreign keys. Is there something I can do to work around this DROP TABLE foreign keys problem?

Solution

With MySQL — and any other database — any time you want to rebuild your database schema, the first thing you normally do is drop all your old database tables with MySQL drop table statements, and then rebuild them with MySQL create table statements. When you do this, you'll often run into problems dropping the old database tables because of the foreign key relationships between the tables.

For instance, if an orders table has a foreign key link back to a customers table, you can't drop the customers table until you first drop the orders table — and any other database table that has a foreign key relationship back to the customers table. Until I knew how to properly approach this problem I used to write my DDL (the MySQL drop table statements) in a very specific order — which was very time-consuming — but fortunately there's a much easier solution to this problem with MySQL.

In short, MySQL has a variable named FOREIGN_KEY_CHECKS that you can set just before and just after all your MySQL DROP TABLE statements, and with this variable set, the order of your drop statements doesn't matter. MySQL essentially turns off the foreign key checks, letting you drop your tables in any order desired. After that, you run your MySQL CREATE TABLE statements, then turn the foreign_key_check back on.

MySQL DROP TABLE foreign keys simple example - The old way

Here's a simple example of how this works. First, imagine that you have database tables named customers, orders, order_details, and orders has a foreign key back to customers, and order_details has a foreign key back to orders.

The way I used to do this was drop these tables in this exact order:

drop table if exists order_details;
drop table if exists orders;
drop table if exists customers;

That was the only order in which the MySQL tables could be dropped.

MySQL DROP TABLE foreign keys - The better way

But fortunately, with the MySQL FOREIGN_KEY_CHECKS variable, you don't have to worry about the order of your DROP TABLE statements at all, and you can write them in any order you like -- even the exact opposite -- like this:

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists customers;
drop table if exists orders;
drop table if exists order_details;
SET FOREIGN_KEY_CHECKS = 1;

That's all you have to do for this MySQL DROP TABLE foreign key technique; just set the MySQL FOREIGN_KEY_CHECKS variable before and after all your drop statements, and you're DROP TABLE statements will work just fine, regardless of foreign keys.

This is a very simple example, but hopefully you can see how this will help when your database schema has 10, 20, or more tables with MySQL foreign key relationships.