SQL SERVER 主键和外键中的 级联删除（ON DELETE CASCADE）和级联更新（ON UPDATE CASCADE）

建立学生表
create table gh_student(
stuno varchar(3) primary key,
stuname varchar(4),
stuclass varchar(3)
)

插入数据

insert into gh_student values('001','gh','101')
insert into gh_student values('002','lxg','102')
insert into gh_student values('003','hs','103')

建立成绩表
create table gh_grade(
grade_stuno varchar(3) ,
grade_lessonno varchar(3) ,
grade  varchar(3)
)

插入数据
insert into gh_grade values('001','yw','98')
GO
insert into gh_grade values('001','yy','99')
GO
insert into gh_grade values('001','sx','94')
GO
insert into gh_grade values('002','yw','93')

insert into gh_grade values('002','yy','95')

insert into gh_grade values('002','sx','96')

在成绩表上建立外键约束 这里是级联更新
alter table gh_grade add constraint FK_StudentNo foreign key (grade_stuno) references gh_student (stuno) ON UPDATE CASCADE

此时执行select * from gh_grade

原始结果如下

grade_stuno grade_lessonno grade
001                yw                       98
001                yy                        99
001                sx                        94
002                yw                       93
002                yy                        95
002                sx                        96

注意此时演示级联更新

 执行sql：update gh_student set stuno='008' where stuname='gh'

再次执行查询语句：select * from grade

结果如下

grade_stuno grade_lessonno grade
008                yw                       98
008                yy                        99
008                sx                        94
002                yw                       93
002                yy                        95
002                sx                        96

接下来演示级联删除

建立级联删除

alter table gh_grade DROP constraint FK_StudentNo

alter table gh_grade add constraint FK_StudentNo foreign key (grade_stuno) references gh_student (stuno) ON DELETE CASCADE

然后执行sql：DELETE FROM gh_student WHERE stuno='001'

再次执行查询：select * from grade

结果如下

grade_stuno grade_lessonno grade
002                     yw                 93
002                     yy                  95
002                     sx                  96

总结：所谓的级联更新，就是更新主键表（gh_student ）的同时，外键表(grade)同时更新。

所谓的级联删除，就是删除主键表（gh_student ）的同时，外键表(grade)同时删除。

这里还要注意：当学生表（gh_student ）中没有('001','gh','101')这条记录时，成绩表(grade)中这三条插入语句是插不进去的，会报冲突，成绩表会自动判断没有001这个学生。

insert into gh_grade values('001','yw','98')
GO
insert into gh_grade values('001','yy','99')
GO
insert into gh_grade values('001','sx','94')
GO