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. This is a typical many-to-one relationship with user tables and user groups, and multiple users correspond to one user group. First create User Group table: Create User Group table create tables T_group ( id int not null, name varchar (30) , primary key (ID) ); and insert two records: insert record insert into t_group values (1, ' Group1 '); insert Into T_group values (2, ' Group2 '); create a user table below, creating a foreign key reference relationship in a different way: 1, Cascade (Cascade) cascading CREATE TABLE T_user ( id int not null, name varchar (), groupid int, primary key (ID), foreign Key (GroupID) references T_group (ID) on 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, does not conform to referential integrity constraints     constrain mode test insert into t_user values (1, ' qianxin ', 1); insert to T_user values (2, ' Yiyu ', 2); insert in To 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 (), groupid int, primary key (ID), foreign key (Gr Oupid) 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, and referential integrity constraints do not match constraint 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 recorded GroupID is set to Null update T_group set id=2 where id=1; #导致t_user中的1记录的groupid被设置为NULL 3, Prohibition (no action/restrict) mode Forbidden Mode create table T_user ( id int not null, name varchar (), groupid int, primary key (ID), & Nbsp;foreign Key (GroupID) 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, and referential integrity constraints do not match constraint test insert into t_user values (1, ' qianxin ', 1); INSERT into T_user values (2, ' Yiyu ', 2), insert to T_user values (3, ' Dai ', 2); delete from T_group where id= 2; #错误, there are references in the table, so update t_gr cannot be deleted from the main tableOUP set id=2 where id=1; #错误, 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.
Transferred from: http://www.2cto.com/database/201302/190165.html
MySQL FOREIGN KEY constraint
