MySQL implements CASCADE operations (CASCADE UPDATE, Cascade Delete)

Source: Internet
Author: User

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';


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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.