MySQL FOREIGN KEY constraint

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.

The foreign key is used to guarantee the integrity and consistency of the data, and the insertion operation fails by checking the foreign key for incorrect deletion.

Different foreign KEY constraints will allow the two tables to be tightly combined, especially if the modified or deleted cascade operation will make the routine maintenance work easier.

　　

The trigger can also complete the cascade operation to modify or delete the operation, the difference of 2 is as follows (own understanding):

1. Triggers are more resource-intensive, operations on the original table and cascading operations from the table are in one transaction.

2. The data volume of the table is not big, the structure is not complex, the foreign key constraint is preferred.

3. Triggers can accomplish more than foreign key constraints, there are complex logic such as if...else ..., you can only use triggers to complete.

Here is an example of a user table and a user group table for a FOREIGN key constraint, which is a typical many-to-one relationship where multiple users correspond to a user group.

Create a user Group table first: The CREATE TABLE t_group (ID int not NULL, name varchar (), primary key (ID));　　and insert two records: Insert record inserts into T_group values (1, ' Group1 '); 　　INSERT into T_group values (2, ' Group2 '); Create a user table below to create a foreign key reference relationship with different constraints: 1. Cascade (CASCADE) modeCREATE TABLE T_user (ID int not NULL, name varchar (+), GroupID int, primary key (ID), FOREIGN key (GroupI  　　d) references T_group (ID) on the DELETE cascade on UPDATE cascade); Referential integrity test INSERT INTO T_user values (1, ' qianxin ', 1);    #可以插入 INSERT INTO T_user values (2, ' Yiyu ', 2);   #可以插入 INSERT into T_user values (3, ' Dai ', 3);　　#错误, unable to insert, user Group 3 does not exist, inconsistent with referential integrity constraints test INSERT INTO T_user values (1, ' qianxin ', 1);　　INSERT into T_user values (2, ' Yiyu ', 2);　　INSERT into T_user values (3, ' Dai ', 2);              Delete from T_group where id=2;         #导致t_user中的2, 3 record cascade Delete Update T_group set id=2 where id=1; #导致t_user中的1记录的groupid级联修改为2 2. Empty (set NULL) modeCREATE TABLE T_user (ID int not NULL, name varchar (+), GroupID int, primary key (ID), FOREIGN key (GroupI 　　d) references T_group (ID) on delete set NULL on update set NULL); Referential integrity test INSERT INTO T_user values (1, ' qianxin ', 1);    #可以插入 INSERT INTO T_user values (2, ' Yiyu ', 2);     #可以插入 INSERT into T_user values (3, ' Dai ', 3);　　#错误, unable to insert, user Group 3 does not exist, inconsistent with referential integrity constraints test INSERT INTO T_user values (1, ' qianxin ', 1);　　INSERT into T_user values (2, ' Yiyu ', 2);　　INSERT into T_user values (3, ' Dai ', 2);             Delete from T_group where id=2;         The GroupID of the #导致t_user中的2, 3 record is set to NULL update T_group set id=2 where id=1; #导致t_user中的1记录的groupid被设置为NULL 3. Prohibit (no action/restrict) modeCREATE TABLE T_user (ID int not NULL, name varchar (+), GroupID int, primary key (ID), FOREIGN key (GroupI 　　d) references T_group (id) on Delete No. action on Update No action); Referential integrity test INSERT INTO T_user values (1, ' qianxin ', 1);    #可以插入 INSERT INTO T_user values (2, ' Yiyu ', 2);     #可以插入 INSERT into T_user values (3, ' Dai ', 3);　　#错误, unable to insert, user Group 3 does not exist, inconsistent with referential integrity constraints test INSERT INTO T_user values (1, ' qianxin ', 1);　　INSERT into T_user values (2, ' Yiyu ', 2);　　INSERT into T_user values (3, ' Dai ', 2);              Delete from T_group where id=2;          #错误, there are related references from the table, so the update t_group set id=2 where id=1 cannot be removed from the primary table; #错误, there are related references from the table, so the main table cannot be modified note: In MySQL, the Restrict mode works the same as the no action mode.

MySQL FOREIGN KEY constraint