A MySQL foreign keys “drop table” and re-create table example

By Alvin Alexander. Last updated: July 16, 2019

MySQL/MariaDB FAQ: How can I drop a series of database tables that have foreign key relationships between them?

Solution

If you ever have a problem where you need to drop a series of MySQL database tables that have foreign key relationships between them, the key to doing this is setting the FOREIGN_KEY_CHECKS variable before and after your MySQL DROP TABLE queries.

For example, something like this should work:

# 1) tell mysql to ignore foreign keys for a little while
SET FOREIGN_KEY_CHECKS = 0;

# 2) drop your tables
drop table if exists foo;
drop table if exists bar;
drop table if exists baz;

# 3) turn the mysql foreign keys check back on
SET FOREIGN_KEY_CHECKS = 1;

I explain this more in the section that follows, but that’s the basic technique/solution.

Dropping and re-creating MySQL tables with FOREIGN_KEY_CHECKS

This is pretty obscure, but I thought I'd post it here so I wouldn't forget how to do this ... if you ever have a situation when using MySQL where:

You're just starting to use a database table
The table joins to other tables
The table doesn't have any data in it (or possibly doesn't have much data in it)
You want to change the MySQL table schema
You may be able to use this technique

In my case I have a MySQL database table named 'entities' that looked fine when I was designing the table, but as I began using it, I realized there were a few things I wanted to change. I may have been able to make these changes using the MySQL ALTER TABLE syntax — I don't know, I didn't have to look it up — but instead I was able to use a MySQL foreign keys drop table trick/tecnique I wrote about long ago.

In short, to make the changes to my MySQL database table named entities, I issued these commands to drop and then re-create the database table, even though it had foreign keys to other database tables, and other tables had foreign keys to it:

# tell mysql to ignore foreign keys for a little while
SET FOREIGN_KEY_CHECKS = 0;

# drop my old database table
drop table if exists entities;

# re-create my table
create table entities (
  id int unsigned auto_increment not null primary key,
  project_id int unsigned not null,
  name varchar(64) not null,
  type character(3),
  num_rets int,
  num_dets int,
  complexity varchar(7),
  foreign key (project_id) references projects (id) on delete cascade
) ENGINE = InnoDB;

# turn the mysql foreign keys check back on
SET FOREIGN_KEY_CHECKS = 1;

As mentioned, this is a bit of an obscure technique, combining this MySQL foreign keys check with the need to drop a database table and then re-create the table, while the table had very little data in it, but hey, if it helps anyone else, I'm happy with it.

mysql

create table