Two ways to update MySQL cascade: Trigger Update and FOREIGN key

There are two ways of 1.mysql cascading updates: Trigger updates and foreign key updates.

2. Trigger updates and foreign key updates are intended to ensure data integrity.

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.

As an example:

Existing 2 entities-mahjong machine students, courses, 1 links-Achievements

Create student table students, curriculum course, score table score

--Create student table students

CREATE TABLE IF not EXISTS ' students ' (

' id ' int (one) not NULL auto_increment,

' Name ' varchar (+) DEFAULT "",

PRIMARY KEY (' id ')

) Engine=innodb;

--Insert several records

INSERT into ' students ' (' ID ', ' name ') VALUES

(1, "John"),

(2, "Lucy"),

(4, "Jack");

--Create a curriculum

CREATE TABLE IF not EXISTS ' course ' (

' id ' int (one) not NULL auto_increment,

' Name ' varchar (+) DEFAULT "",

PRIMARY KEY (' id ')

) Engine=innodb;

--inserting data into several

INSERT into ' course ' (' id ', ' name ') VALUES

(1, "中文版"),

(2, "Chinese"),

(3, "math");

--Create a score table

--sid Student ID

--CID Course ID

CREATE TABLE IF not EXISTS ' score ' (

' Sid ' Int (one) DEFAULT "0",

' CID ' int (one) DEFAULT "0",

' Score ' float (6,2) DEFAULT "0.00",

KEY ' Sid ' (' Sid '),

KEY ' CID ' (' CID ')

) Engine=innodb;

--Inserting several data

INSERT into ' score ' (' Sid ', ' CID ', ' score ') VALUES

(1, 2, 95.00),

(1, 3, 65.00),

(2, 1, 77.00),

(2, 2, 68.50),

(2, 3, 89.00);

Now, I want to:

Delete the record of the students table, and automatically delete the records of the students in the score table

Delete the records of the course table and automatically delete the record of the course in the score table

I think of the practice there are two:

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.

FOREIGN KEY constraint document see: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

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

//

Triggers are better understood, where after is the event, some requirements may be before; event types are insert,replace,update,delete, etc.

Here the "//" is delimiter, used to mark the start and end of the trigger

Two ways to update MySQL cascade: Trigger Update and FOREIGN key