Three ways to constrain foreign keys

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:

SQL code

1. Create table T_group (
2. ID int not null,
3. name varchar (+),
4. primary key (ID)
5. );

CREATE TABLE T_group (ID int not null,name varchar (), primary key (ID));

and insert two records:

SQL code

1. Insert INTO t_group values (1, ' Group1 ');
2. Insert INTO t_group values (2, ' Group2 ');

INSERT into T_group values (1, ' Group1 ') and 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) mode

SQL code

2. Create table T_user (
4. ID int not null,
6. name varchar (+),
8. GroupID int,
10. primary key (ID),
12. Foreign Key (GroupID) references T_group (ID) on delete cascade on update Cascade
14. );

CREATE TABLE T_user (ID int not null,name varchar (), GroupID int,primary key (ID), foreign key (GroupID) references T_grou P (ID) on the DELETE cascade on UPDATE cascade);

Referential integrity Testing

SQL code

1. insert into t_user  values  (1,  ' qianxin ',  1);  --can be inserted    
2. insert into t_user values   (2,  ' Yiyu ',  2);     --can be inserted     
3. insert  into t_user values  (3, --error, unable to insert, user Group 3 does not exist, inconsistent with referential integrity constraints   

INSERT into T_user values (1, ' qianxin ', 1); --INSERT into t_user values (2, ' Yiyu ', 2) can be inserted;    --INSERT into t_user values (3, ' Dai ', 3) can be inserted;    --Error, unable to insert, user Group 3 does not exist, does not match referential integrity constraint

Constraint mode test

SQL code

2. Insert INTO t_user values (1, ' qianxin ', 1);
4. Insert INTO t_user values (2, ' Yiyu ', 2);
6. Insert INTO t_user values (3, ' Dai ', 2);
8. Delete from t_group where id=2; --Causes 2, 3 record cascade deletions in T_user
10. Update T_group set id=2 where id=1; --GroupID cascade modification of 1 records in T_user to 2

INSERT into T_user values (1, ' qianxin ', 1), insert into t_user values (2, ' Yiyu ', 2); INSERT into T_user values (3, ' Dai ', 2);d elete from T_group where id=2;              --Results in T_user 2, 3 record cascade Delete Update T_group set id=2 where id=1;          --GroupID cascade modification of 1 records in T_user to 2

2. Empty (set NULL) mode

SQL code

2. Empty mode
4. Create table T_user (
6. ID int not null,
8. name varchar (+),
10. GroupID int,
12. primary key (ID),
14. Foreign Key (GroupID) references T_group (ID) on delete set null on update set null
16. );
20. Referential integrity Testing
24. Insert INTO t_user values (1, ' qianxin ', 1); --can be inserted
26. Insert INTO t_user values (2, ' Yiyu ', 2); --can be inserted
28. Insert INTO t_user values (3, ' Dai ', 3); --Error, unable to insert, user Group 3 does not exist, does not match referential integrity constraint
32. Constraint mode test
34. Insert INTO t_user values (1, ' qianxin ', 1);
36. Insert INTO t_user values (2, ' Yiyu ', 2);
38. Insert INTO t_user values (3, ' Dai ', 2);
40. Delete from t_group where id=2; --Causes the GroupID of 2, 3 records in T_user to be set to null
42. Update T_group set id=2 where id=1; --The groupid that caused the 1 record in T_user is set to null

Empty mode CREATE TABLE T_user (ID int not null,name varchar (), GroupID int,primary key (ID), foreign key (GroupID) 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) can be inserted;    --INSERT into t_user values (3, ' Dai ', 3) can be inserted;     --Error, 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); ins ert into T_user values (3, ' Dai ', 2);d elete from T_group where id=2;              --Causes the GroupID of 2, 3 records in T_user to be set to Nullupdate T_group set id=2 where id=1;          --the GroupID that caused the 1 record in T_user is set to null

3. Prohibit (no action/restrict) mode

SQL code

2. Prohibited mode
4. Create table T_user (
6. ID int not null,
8. name varchar (+),
10. GroupID int,
12. primary key (ID),
14. Foreign Key (GroupID) references T_group (ID) on Delete no action on update no action
16. );
20. Referential integrity Testing
22. Insert INTO t_user values (1, ' qianxin ', 1); --can be inserted
24. Insert INTO t_user values (2, ' Yiyu ', 2); --can be inserted
26. Insert INTO t_user values (3, ' Dai ', 3); --Error, unable to insert, user Group 3 does not exist, does not match referential integrity constraint
30. Constraint mode test
32. Insert INTO t_user values (1, ' qianxin ', 1);
34. Insert INTO t_user values (2, ' Yiyu ', 2);
36. Insert INTO t_user values (3, ' Dai ', 2);
38. Delete from t_group where id=2; --Error, there is a correlation reference from the table, so the primary table cannot be deleted
40. Update T_group set id=2 where id=1; --error, related reference from table, so cannot be modified in main table

Three ways to constrain foreign keys