SQL中更新或修改主键的值

发表于

1. 问题导入

我们应该选取相对固定的属性作为主键，也就是说主键的值轻易不要更改。但是凡事皆有例外，有时候确实需要更改主键的值，比如学生的学号录入错了，且已经录入了成绩，如果直接修改学号的值，会因为外键约束的原因无法修改。

具体来说，我们考虑三个表：

  1. 学生表s，其中的属性有学号sno（主键），姓名sname，性别sex
  2. 课程表c，其中的属性有课程号cno（主键），课程名称cname，学费credit
  3. 成绩表sc，其中的属性有学号sno，课程号cno，成绩score

sc中sno和cno共同作为sc的主键且分别引用s和c中的sno，cno作为外键。

假设我们想把学号为10010的同学的学号改为10001，很容易想到如下的SQL语句

1
2
3
update s
set sno=10001
where sno=10010

但是运行的话可能会因为外键约束而拒绝执行。

2. 为什么不能运行

如果sc中已经有引用关于sno=10010的记录了（比如10010这位学生修了课程号为1的这门课，成绩为90分），那么此时如果修改s表中sno的值，sc中的sno作为引用了s中的sno的外键无法跟着改变，便会爆出外键约束的错误。

3. 应该怎么做

将外键约束改为级联(cascade)即可，也就是说当修改主键的值的时候，引用他的外键的值会跟着更改，具体代码如下：

1
2
3
4
5
alter table sc
add constraint fk_sc_sno_1
foreign key (sno)
references s(sno)
on update cascade

on update cascade声明了在update操作时执行级联操作。

类似的，如果一个已经被引用的元组无法删除，同样需要声明级联操作，将上述代码中的update改为delete即可（级联删除：引用该主码的元组会跟着删除）具体如下：

1
2
3
4
5
alter table sc
add constraint fk_sc_sno_2
foreign key (sno)
references s(sno)
on delete cascade

另外多啰嗦一句关于约束(constraint)的命名，正如上面代码所示fk_sc_sno_1，fk表明是外键（foreign key），sc_sno说明这个外键约束作用在sc这个表的sno这个属性上，至于最后末尾的数字是用于再区分的，因为作用在sc这个表的sno这个属性上的外键约束可能不止一个，比如本文就是两个。