MySQL foreign key creation and deletion

Creation of foreign keys

Method 1: Set the table when creating (the foreign key name is randomly generated)

1. Prerequisites, must have a master table, which is set to persons

2. Primary key field must be set in Main Table primary key, this is set to Id_p

Create DATABASE (test) create exists test character Set utf8;//creates a primary table (persons) creating table if not exists Perso NS (    id_p int not NULL,    lastName varchar (at) not NULL,    firstName varchar (20) is not NULL,    address varchar ) is not NULL, the city varchar () is not    null,    primary KEY (id_p)//sets the primary key, which must be set here);

Inserting data

Created from table (orders) CREATE table if not EXISTS orders (    Id_o int not NULL,    OrderNo int. NOT NULL,    id_p int. not null,< C11/>primary Key (Id_o),    foreign Key (id_p) references persons (id_p)  //Set foreign keys, two field names id_p do not need to be consistent, but data types must be the same);

Inserting data

　　

Method 2: Create a foreign key name in the way set

ALTER TABLE orders add constraint fk_id foreign key (id_p) references persons (id_p);

　　

Deletion of foreign keys

1. First find out its foreign key

Show create table orders;

2. Delete foreign keys

ALTER TABLE orders drop foreign key order_ibfk_1;

　　

MySQL foreign key creation and deletion