MySQL---cascade Update and DELETE operations

We usually have this requirement: delete the records in Table 1, and you need to delete several records related to table 1 in the other tables.

For this, we have two workarounds:

One, foreign key constraints using the InnoDB table

ALTER TABLE ' score '

ADD CONSTRAINT ' STUDENT_IBFK1 '

FOREIGN KEY ' Sid ' (' Sid ') REFERENCES ' students ' (' ID ')

On DELETE CASCADE on UPDATE CASCADE;

The cascade effect here is that the child table updates or deletes the corresponding records when the parent table records are updated or deleted

The action of the Foreign KEY constraint, in addition to the Cascade, also has the restrict (limit delete) set NULL (set to NULL, if the field is allowed to be empty), etc.

Second, use trigger trigger to operate

Because the foreign KEY constraint can only be used for the InnoDB type table, some of the MyISAM type tables have to be updated with trigger.

--The following trigger deletes the related record in table score after deleting students

DROP TRIGGER IF EXISTS ' Deletescore '

CREATE TRIGGER ' Deletescore ' after DELETE on ' students '

For each ROW BEGIN

DELETE from Score WHERE sid=old. ' ID ';

END

Trigger better understanding, in which after is the event, some requirements may be used before, the event type has insert,replace,update,delete, etc.

For MySQL foreign key constraints, say a few words:

Foreign KEY constraints are divided into three types: Cascade,set null,restrict

Examples to illustrate differences:

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

Thus, the three will be inserted into the same constraints, different reflected in the processing of delete and update operations;

MySQL---cascade Update and DELETE operations