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