MySQL implements CASCADE operations (CASCADE UPDATE, Cascade Delete)

First, create two tables STU,SC

Create TableStu (SidintUNSIGNEDPrimary KeyAuto_increment,namevarchar( -) not NULL) TYPE=InnoDB CharSet=UTF8;Create TableSC (SCIDintUNSIGNEDPrimary KeyAuto_increment,sidintUNSIGNED not NULL, scorevarchar( -)default '0',Index(SID),--foreign keys must be indexedFOREIGN KEY(SID)REFERENCESStu (SID) on DELETE CASCADE  on UPDATE CASCADE) TYPE=InnoDB CharSet=UTF8;

--Description: Foreign keys must be indexed;

FOREIGN key (SID) Set foreign key, set SID to foreign key

REFERENCES Stu (SID) reference function. Referencing SIDs in the Stu table

On Delete CASCADE Cascade Delete
On UPDATE CASCADE cascade update

Ii. inserting data into two sheets

Insert  intoStu (name) value ('ZXF');Insert  intoStu (name) value ('ls');Insert  intoStu (name) value ('ZS');Insert  intoStu (name) value ('ww');Insert  intoSC (Sid,score)Values('1','98');Insert  intoSC (Sid,score)Values('1','98');Insert  intoSC (Sid,score)Values('2',' the');Insert  intoSC (Sid,score)Values('2','98');Insert  intoSC (Sid,score)Values('2','98');Insert  intoSC (Sid,score)Values('3',' About');Insert  intoSC (Sid,score)Values('4',' +');Insert  intoSC (Sid,score)Values('4','98');

Note: When inserting data into the SC table, if the inserted SID is 22, the insert fails, violating the foreign key constraint, because the foreign key SID
The primary key from the ID in the Stu table, that is, the ID in Stu does not have data equal to 22.

Cascade Delete: Delete the student with ID 2 in the Stu table, and the student's scores in the SC table will also be deleted in Cascade

Delete  from where = ' 2 ';

Cascade Update: The Student with ID 3 in the Stu table is changed to ID 6, and the student's corresponding ID in the SC table is also cascade updated

Update set sid=6where sid='3';

Attention

You must delete the Foreign key table (SC) Before deleting the table, and then delete the primary key table (STU).

To violate foreign key constraints, you cannot delete

For normal deletion, remove the SC table First, then delete the Stu table!

MySQL implements CASCADE operations (CASCADE UPDATE, Cascade Delete)