To add a foreign key to a table must satisfy 2 conditions, 1, the engine of the table must be innodb,2, you must index the field to which you want to add the foreign key
CREATE TABLE Child (
CID int NOT NULL Auto_increment primary key,
PID int,
Key PID (PID),
CNAME varchar (20));
Add a foreign key by alter
ALTER TABLE child add constraint PIDs foreign key (PID) references parent (parent_id) on DELETE cascade on UPDATE cascade;
Add a foreign key when creating a table
CREATE TABLE Child (
CID int NOT NULL Auto_increment primary key,
PID int,
Key PID (PID),
CNAME varchar (20),
Constraint PIDs foreign KEY (PID) references parent (parent_id) on DELETE cascade on UPDATE cascade
);
You can also create a table without a foreign key name, which is created by default, such as:
CREATE TABLE Child (
CID int NOT NULL Auto_increment primary key,
PID int,
Key PID (PID),
CNAME varchar (20),
Foreign KEY (PID) references parent (parent_id) on DELETE cascade
);
Delete foreign key
ALTER TABLE child drop FOREIGN key PIDs
Description
On delete/on update to define the Delete,update operation. The following are the various constraint types for update,delete operations:
CASCADE:
Foreign key table foreign key field value will be updated, or the column will be deleted.
RESTRICT:
Restrict is also equivalent to no action, that is, no action is made. That is, deny parent table Update FOREIGN Key Association column, delete record.
Set NULL:
When the Foreign Key association field of the parent is update, delete, the foreign key column of the child table is set to NULL.
For insert, the value entered for the foreign key column of the child table can only be the value that is already in the parent table's Foreign Key Association column. Otherwise, an error occurs.
About foreign key additions and deletions operations