MySQL FOREIGN KEY constraint summary

Summarize three kinds of MySQL foreign key constraint mode

If Table A's primary key is a field in table B, the field is called the Foreign key of Table B, table A is called the primary table, and table B is called from the table. Foreign keys are used to achieve referential integrity, and different foreign key constraints will allow the two tables to be tightly combined, especially if the modified or deleted cascade operation will make routine maintenance easier. Here we take MySQL as an example to summarize the differences and connections between the 3 foreign key constraints.

Here, for example, the user and User groups table, this is a typical many-to-one relationship where multiple users correspond to a single user group.

First create the User Group table:

Create a user Group table

Create Table  intnotnull varchar (in), primary   key  (ID));

and insert two records:

Inserting records

Insert  into Values (1'Group1'); Insert  into Values (2'Group2');

Create a user table below to create a foreign key reference relationship with different constraints:

1. Cascade (CASCADE) mode

Cascade mode

Create TableT_user (IDint  not NULL, namevarchar( -), GroupIDint,Primary Key(ID),Foreign Key(GroupID)ReferencesT_group (ID) on Delete Cascade  on Update Cascade);

Referential integrity Testing

Insert  intoT_userValues(1,'qianxin',1); #可以插入Insert  intoT_userValues(2,'Yiyu',2); #可以插入Insert  intoT_userValues(3,'Dai',3); #错误, unable to insert, user Group 3 does not exist, does not match referential integrity constraints

Constraint mode test

Insert  intoT_userValues(1,'qianxin',1);Insert  intoT_userValues(2,'Yiyu',2);Insert  intoT_userValues(3,'Dai',2);Delete  fromT_groupwhereId=2; #导致t_user中的2, 3 record cascade deleteUpdateT_groupSetId=2 whereId=1; #导致t_user中的1记录的groupid级联修改为2

2. Empty (set NULL) mode

Empty mode

Create TableT_user (IDint  not NULL, namevarchar( -), GroupIDint,Primary Key(ID),Foreign Key(GroupID)ReferencesT_group (ID) on Delete Set NULL  on Update Set NULL);

Referential integrity Test Insert intoT_userValues(1,'qianxin',1); #可以插入Insert  intoT_userValues(2,'Yiyu',2); #可以插入Insert  intoT_userValues(3,'Dai',3); #错误, unable to insert, user Group 3 does not exist, does not match referential integrity constraints

Constraint mode test

Insert  intoT_userValues(1,'qianxin',1); Insert  intoT_userValues(2,'Yiyu',2); Insert  intoT_userValues(3,'Dai',2); Delete  fromT_groupwhereId=2; The groupid of the #导致t_user中的2, 3 record is set to nullUpdateT_groupSetId=2 whereId=1; #导致t_user中的1记录的groupid被设置为NULL

3. Prohibit (no action/restrict) mode

Prohibited mode

Create TableT_user (IDint  not NULL, namevarchar( -), GroupIDint,Primary Key(ID),Foreign Key(GroupID)ReferencesT_group (ID) on DeleteNo action on Updateno action);

Referential integrity Testing

Insert  intoT_userValues(1,'qianxin',1); #可以插入Insert  intoT_userValues(2,'Yiyu',2); #可以插入Insert  intoT_userValues(3,'Dai',3); #错误, unable to insert, user Group 3 does not exist, does not match referential integrity constraints

Constraint mode test

Insert  intoT_userValues(1,'qianxin',1); Insert  intoT_userValues(2,'Yiyu',2); Insert  intoT_userValues(3,'Dai',2); Delete  fromT_groupwhereId=2; #错误, there are related references from the table, so the primary table cannot be deletedUpdateT_groupSetId=2 whereId=1; #错误, there are related references from the table, so the primary table cannot be modified

Note: In MySQL, the Restrict mode works the same as the no action mode.

MySQL FOREIGN KEY constraint summary