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

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.

Add new comment

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.