Mysql实现级联操作（级联更新、级联删除）

2023-08-03 10:32:22

一、首先创建两张表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 '',

index (sid),   --外键必须加索引

FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE ON UPDATE CASCADE)

TYPE=InnoDB charset=utf8;

--说明：外键必须建立索引；

FOREIGN key(sid) 设置外键，把sid设为外键

REFERENCES stu(sid) 引用作用。引用stu表中的sid

ON DELETE CASCADE 级联删除
ON UPDATE CASCADE 级联更新

二、向两张表插入数据

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 ('','');

insert into sc(sid,score) values ('','');

insert into sc(sid,score) values ('','');

insert into sc(sid,score) values ('','');

insert into sc(sid,score) values ('','');

insert into sc(sid,score) values ('','');

insert into sc(sid,score) values ('','');

insert into sc(sid,score) values ('','');