MySQL参照完整性一般是通过MySQL外键(foreign key)实现的。
外键(仅innoDB支持)所引用表的列必须是主键。
外键声明包括三个部分:
A、哪个列或列组合是外键
B、指定外键参照的表和列
C、参照动作[cascade(级联操作),restrict(拒绝操作),set null(设为空),no action,set default]。
如果外键约束指定了参照动作,主表记录做修改,删除,从表引用的列会做相应修改,或不修改,拒绝修改或设置为默认值。
引用表的列名必须是主键,且在删除引用表时必须删除引用关系或者删除当前表。
2、创建表时指定外键
创建两张表,学生表student和成绩表score,成绩表的sid列的取值参照学生表(学生表student的sid列设置为主键,且表的存储引擎为innodb,成绩表score的存储引擎也必须设置为innodb)。
3、删除参照约束
ALTER TABLE
scoreDROP FOREIGN KEY
score_fk;
4、给现有表增加参照约束
ALTER TABLE
scoreADD CONSTRAINT
score_fk2FOREIGN KEY (
sid) REFERENCES
student(
sid);
5、验证级联动作删除和更新
在score表创建的参照完整性,删除动作和更新动作的参照动作选择了cascade(级联操作),当学生表的sid更新时,分数表score的相应的sid也会更新,当学生被删除,分数表对应的sid的记录也会自动删除。
更新学生表学号是1的学生的学号为10
update student set sid=10 where sid=1
查看成绩表,可以看到以前学号是1的已经变成了10
select * from score
删除学生表学号是2的学生
delete from student where sid=2
可以看到成绩表,该学生的成绩已经级联删除
select * from score
6、验证级联动作No Aaction
级联动作设置为NO ACTION,如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作。
Restrict动作同no action,都是立即检查外键约束。
将参照动作设置为no action,如果成绩表score有该学生sid,将不能更改学生表student表的学生sid列,也不能删除该学生。除非你先删除该学生的成绩,再删除该学生。
删除score表的外键约束
ALTER TABLE
scoreDROP FOREIGN KEY
score_fk;
增加score表的sid列外键约束
更新学号是10的学生的学号,失败
update student set sid=11 where sid=10
删除学号是10的学生,失败
delete from student where sid=10
需要先删除该学生成绩表中的记录,再删除该学生。
7、验证级联动作Set NULL
在父表上update/delete记录时,将子表上匹配记录的列设为null ,要注意子表的外键列不能为not null。
删除成绩表的外键约束
ALTER TABLE
scoreDROP FOREIGN KEY
score_fk;
增加成绩表的sid列外键约束,参照动作为set null
ALTER TABLE
scoreADD CONSTRAINT
score_fkFOREIGN KEY (
sid) REFERENCES
student(
sid) ON DELETE SET NULL ON UPDATE SET NULL;
修改成绩表的sid列默认值为NULL
删除学生表中学号1的学生
delete from student where sid=1
查看成绩表,成绩表中学号为1的列为NULL
select * from score