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

Summary:how to drop MySQL database tables and recreate them if you have foreign keyrelationships between the T Ables.

This is pretty obscure, but I thought I ' d post it It's I wouldn ' t forget how to do this ... if you ever has a situatio n When using MySQL where:

• You ' re just starting to use a database table
• The table joins to other tables
• The table doesn ' t has any data in it (or possibly doesn ' t has much data in it)
• Want to change the MySQL table schema
• Able to use this technique

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

In short, to do the changes to my MySQL database table named entities, I issued these commands to drop and then re-creat E 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 in 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 (+) 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 a obscure technique, combining this MySQL foreign keys check with the need to drop a datab ASE 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