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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.