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

標籤：

 

Summary: How to drop MySQL database tables and recreate them when you have foreign keyrelationships 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 whileSET FOREIGN_KEY_CHECKS = 0;# drop my old database tabledrop table if exists entities;# re-create my tablecreate 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 onSET 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.

 

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