A MySQL foreign keys drop table, re-create table example

Summary: How to drop MySQL database tables and recreate them when you have foreign key relationships between the tables.

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.