MySQL 数据完整性
数据库实验回顾
-
实体完整性
实体完整性即主码的属性不能为空。而主码就可保证元组是不重复的，即主码值是不能重复的。
-
参照完整性
参照完整性保证外码的值要么是被参照关系中的主码值，要么取空值。
-
用户自定义完整性
可以按系统的需求设计各种自定义的完整性检查。
一、实体完整性
1、主键约束
主键（promary key）用于唯一的标识表中的某一条记录，在两个表的关系中，主键用来在一个表中引用来自另一个表中的特定记录。一个表的主键可以由多个关键字共同组成，并且主键的列不能包含空值。主键的值能唯一标识表中的每一行，这就好比所有人都有身份证，每个人的身份证号是不同的，能唯一标识每一个人。
添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
设置主键
ALTER TABLE orders ADD PRIMARY KEY(列名);
在创建表的时候，设置主键
-- 单个字段的主键
CREATE TABLE 表名(
字段名 数据类型 PRIMARY KEY
);
-- 多个字段组合的主键
CREATE TABLE 表名(
字段名1 数据类型,
字段名2 数据类型,
.....
PRIMARY KEY(字段名1, 字段名2, 字段名n)
);
2、唯一约束
唯一约束用于保证数据表中字段值的唯一性，在 MySQL 中使用 UNIQUE 关键字添加唯一约束。在创建表时为某个字段添加唯一约束的具体语法格式如下：
CREATE TABLE 表名(
字段名 数据类型 UNIQUE,
....
);
注意：被定义成唯一约束的字段，字段值不能相同。但是可以为 NULL。
唯一约束也可以添加到已经创建完成的表中，语法格式如下：
ALTER TABLE 表名 ADD UNIQUE(列名);
3、自动增长列
数据表中的 id 字段一般从1开始插入，不断增加，每次插入新数据时，都要添加一个 id 字段的值，当数据内容庞大时，容易出错。为了解决这个问题，可以将 id 字段的值设置为自动增加。在 MySQL 中使用 AUTO_INCREMENT 关键字设置表字段值自动增加。在创建表时将某个字段的值设置为自动增长，语法格式如下：
CREATE TABLE 表名(
字段名 数据类型 AUTO_INCREMENT,
....
);
此外，也可以为已经创建完成的表字段设置自动增长列，语法格式如下：
ALTER TABLE 表名 MODIFY 字段名 数据类型 PRIMARY KEY AUTO_INCREMENTL;
二、参照完整性
MySQL参照完整性一般是通过MySQL外键(foreign key)实现的
删除参照约束
ALTER 表 DROP FOREIGN KEY fg_fk;
给现有表增加参照约束
ALTER TABLE`score`ADD CONSTRAINT`score_fk2`FOREIGN KEY (`sid`) REFERENCES`student`(`sid`);
数据库系统的外键值更新模式一般有3种：
-
默认的外键管理是Restict，即限制方式。前面实验我们已经见识了这种方式的作用。
-
CASCADE，即级联方式，可以理解为株连九族，即主键改变后，引用它的外键值自动改变成新主键值来保证参照完整性。
-
SET NULL，置空，即主键值改变后，引用它的外键值自动改为NULL来保证参照完整性。
eg
alter table 表名
add constraint fk_js foreign key(任课教师编号) references teacher (工号) on update cascade;
三、用户自定义完整性
像MS SQL Server等数据库管理系统有CHECK约束可以很方便地实现用户自定义完整性约束。但MySQL没有提供真正的CHECK约束。但用户自定义约束的原理都差不多，通过触发器就可以实现。
例如：学生年龄不能取负值的约束
-- insert 触发器 年龄不能为负
delimiter ;
delimiter $$
create trigger st_ins_chk_age before insert
on student
for each row
begin
if new.年龄 is not NULL and new.年龄 < 0 then
signal sqlstate 'HY000'
set message_text = "年龄不能为负";
end if;
end$$
-- update 触发器 年龄不能为负
delimiter ;
delimiter $$
create trigger st_up_chk_age before update
on student
for each row
begin
if new.年龄 is not NULL and new.年龄 < 0 then
signal sqlstate 'HY000'
set message_text = "年龄不能为负";
end if;
end$$
delimiter ;
附加
不用外键，而利用触发器实现socre的课号要级联参照course中的课号？
score 表
-- 触发器 实现 score 外键 学号 参照 student 学号 -- score
delimiter ;
delimiter $$
create trigger sc_fk_ins_xh before insert
on score
for each row
begin
if (select count(*) from student where 学号=new.学号)=0 and new.学号 is not NULL then
signal sqlstate 'HY000'
set message_text = "Cannot add or update a child row: a foreign key constraint ...";
end if;
end$$
create trigger sc_fk_up_xh before update
on score
for each row
begin
if (select count(*) from student where 学号=new.学号)=0 and new.学号 is not NULL then
signal sqlstate 'HY000'
set message_text = "Cannot add or update a child row: a foreign key constraint ...";
end if;
end$$
delimiter ;
注意：score 中是 new ，student 中是 old
student 表
-- 触发器 实现 score 外键 学号 参照 student 学号 -- student
delimiter ;
delimiter $$
create trigger st_del_xh before delete
on student
for each row
begin
if (select count(*) from score where 学号=old.学号)>0 then
signal sqlstate 'HY000'
set message_text = "Cannot delete or update a child row: a foreign key constraint ...";
end if;
end$$
create trigger st_up_xh before update
on student
for each row
begin
if (select count(*) from score where 学号=old.学号)>0 then
signal sqlstate 'HY000'
set message_text = "Cannot delete or update a child row: a foreign key constraint ...";
end if;
end$$
delimiter ;
