MySQL foreign key constraints

MySQL foreign key constraint mode if the primary keyword of Table A is A field in Table B, this field is called the foreign key of Table B, table A is called the primary table, and table B is called the slave table. Foreign keys are used to achieve the integrity of the reference. Different foreign key constraints can closely combine the two tables, in particular, modifying or deleting cascade operations makes daily maintenance easier. Here we take MySQL as an example to summarize the differences and connections between the three foreign key constraints. Www.2cto.com here uses the user table and user group table as an example. This is a typical multi-to-one relationship. Multiple users correspond to one user group. First, create a user group table: create table t_group (id int not null, name varchar (30), primary key (id), and insert two records: insert into t_group values (1, 'group1'); insert into t_group values (2, 'group2'); create a user table below, create foreign key reference relationships with different constraints: 1. cascade 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 upda Te cascade); see 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) can be inserted. # error. insertion fails. User Group 3 does not exist, test the constraints of 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; # causes cascade deletion of 2 and 3 Records in t_user 2 where id = 1; # The groupid cascade of the 1 record in t_user is changed to 2. 2. If you set null, 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 ); 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); # error, cannot be inserted, user group 3 does not Yes, and the constraints do not match the 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 2 and 3 Records in t_user is set to NULL update t_group set id = 2 where id = 1; # As a result, the groupid of the 1 record in t_user is set to NULL. 3. the create table t_user (id int not null, name varchar (30) method is disabled ), groupid int, primary key (id ), Foreign key (groupid) references t_group (id) on delete no action on update no action); reference 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); # error, cannot be inserted, user Group 3 does not exist. Test the insert into t_user values (1, 'qianxin ', 1); insert into t_user values (2, 'yiyu', 2) with constraints that do not match the integrity constraints ); insert into t_user values (3, 'dai', 2); Delete from t_group where id = 2; # error. related references exist in the Table. Therefore, the master table cannot delete update t_group set id = 2 where id = 1; # error, there are references in the table, so the primary table cannot be modified. Note: in MySQL, the restrict method works the same as the no action method.