Mysql implements cascade operations (cascade update and cascade deletion) and mysql implements

Mysql implements cascade operations (cascade update and cascade deletion) and mysql implements

1. Create two tables stu, SC

Create table stu (sid int UNSIGNED primary key auto_increment, name varchar (20) not null) TYPE = InnoDB charset = utf8; create table SC (scid int UNSIGNED primary key auto_increment, sid int UNSIGNED not null, score varchar (20) default '0', index (sid), -- the foreign key must be indexed foreign key (sid) REFERENCES stu (sid) on delete cascade on update cascade) TYPE = InnoDB charset = utf8;

-- Note: The foreign key must be indexed;

FOREIGN key (sid) sets the FOREIGN key and sets the sid as the FOREIGN key.

REFERENCES stu (sid) REFERENCES. Reference sid in stu table

On delete cascade cascading Deletion
ON UPDATE CASCADE UPDATE

2. Insert data into two tables

insert into stu (name) value ('zxf');insert into stu (name) value ('ls');insert into stu (name) value ('zs');insert into stu (name) value ('ww');insert into sc(sid,score) values ('1','98');insert into sc(sid,score) values ('1','98');insert into sc(sid,score) values ('2','34');insert into sc(sid,score) values ('2','98');insert into sc(sid,score) values ('2','98');insert into sc(sid,score) values ('3','56');insert into sc(sid,score) values ('4','78');insert into sc(sid,score) values ('4','98');

 

Note: when inserting data in the SC table, if the inserted sid is 22, the insertion will fail, in violation of the foreign key constraints, because the foreign key sid
The primary key from the id in the stu table, that is, the id in the stu does not have data equal to 22.

Cascade deletion: Delete the student whose id is 2 in the stu table, and the score of the student in the SC table is also deleted.

delete from stu where sid = '2';

 

Cascade update: Students Whose id is 3 in the stu table are changed to id 6. the IDs of the students in the SC table are also updated in cascade mode.

update stu set sid=6 where sid='3';

 

Note:

When deleting a table, you must first Delete the foreign key table (SC) and then the primary key table (stu)

 

Cannot be deleted because it violates foreign key constraints.

To be deleted normally, delete the SC table first and then the stu table!