Reference operation for 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. 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 T_group (

ID int NOT NULL,

Name varchar (30),

Primary KEY (ID)

);

and insert two records:

Inserting records

INSERT 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) mode

Cascade mode

CREATE TABLE T_user (

ID int NOT NULL,

Name varchar (30),

GroupID int,

Primary key (ID),

Foreign KEY (GroupID) references T_group (ID) on DELETE cascade on UPDATE cascade

);

Referential integrity Testing

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, does not match referential integrity constraints

Constraint mode 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) mode

Empty mode

CREATE TABLE T_user (

ID int NOT NULL,

Name varchar (30),

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); #可以插入

INSERT into T_user values (3, ' Dai ', 3); #错误, unable to insert, user Group 3 does not exist, does not match referential integrity constraints

Constraint mode 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) mode

Prohibited mode

CREATE TABLE T_user (

ID int NOT NULL,

Name varchar (30),

GroupID int,

Primary key (ID),

Foreign KEY (GroupID) references T_group (id) on Delete No. action on Update no action

);

Referential integrity Testing

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, does not match referential integrity constraints

Constraint mode 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 primary table cannot be deleted

Update T_group set id=2 where id=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.

Original from the "Bit network", reproduced please keep the original link: http://soft.chinabyte.com/database/493/12549493.shtml

Referential operations for MySQL foreign KEY constraints