数据库原理——第五章:数据库完整性

参考书目《数据库系统概论(第5版)》

数据库的完整性是指数据的正确性相容性

  • 数据的正确性是指数据是符合现实世界语义、反映当前实际状况的(实体完整性和用户定义的完整性)
  • 数据的相容性是指数据库同一对象在不同关系表中的数据是符合逻辑的(参照完整性)

实体完整性

关系模式的实体完整性在CREATE TABLE中用PRIMARY KEY定义
单属性构成的码有两种说明方式:列级约束条件、表级约束条件
多属性构成的码只有一种说明方式:表级约束
实体完整性检查和违约处理:

  • 检验主码值是否唯一,如果不唯一则拒绝插入或修改
  • 检查主码的各个属性是否为空,只要有一个为空就拒绝插入或修改
1
2
3
4
5
例:
CREATE TABLE XXX(
Sno CHAR(20) PRIMARY KEY, /*单属性可以在列级定义主码*/
PRIMARY KEY(Sno,Cno,...), /*多属性只能在表级定义主码*/
);

参照完整性

关系模式的参照完整性在CREATE TABLE中用FORIEGN KEY短语定义哪些属性为外码,用REFERENCES指明这些外码参照哪些表的主码

1
2
3
4
5
例:
CREATE TABLE XXX(
Cno CHAR(20),
FOREIGN KEY(Cno) REFERENCES Course(Cno), /*在表级定义参照完整性*/
);

可能破坏参照完整性的情况及违约处理:

被参照表<=>参照表 违约处理
可能破坏参照完整性<-插入元组 拒绝
可能破坏参照完整性<-修改外码值 拒绝
删除元组->可能破坏参照完整性 拒绝/级联删除/设为空值
修改主码值->可能破坏参照完整性 拒绝/级联删除/设为空值

用户定义的完整性

用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求
CREATE TABLE中定义属性的同时,可以根据应用的要求定义属性上的约束条件,即属性值的限制,包括:

  • 列值非空(NOT NULL
  • 值唯一(UNIQUE
  • 检查列值是否满足一个条件表达式(CHECK短语)

与属性上约束条件的定义类似,在CREATE TABLE语句中可以用CHECK短语定义元组上的约束条件,即元组级的限制

1
2
3
4
5
6
7
8
例:
CREATE TABLE Student(
Sno CHAR(9) UNIQUE NOT NULL, /*要求Sno值唯一且不能为空*/
Sname CHAR(8) NOT NULL,
Sage int CHECK(Sage >= 15 AND Sage <= 50) /*列级CHECK短语*/
Ssex CHAR(2),
CHECK(Ssex='女' OR Sname NOT LIKE'Ms.%') /*定义了元组中两个属性值之间的约束*/
);

完整性约束命名子句

用完整性约束命名子句CONSTRAINT,用来对完整性约束条件命名,从而便于管理完整性约束条件

1
CONSTRAINT <完整性约束条件名> <完整性约束条件>

其中<完整性约束条件>包括NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK

1
2
3
4
5
6
7
8
9
10
例:
CREATE TABLE Student(
Sno NUMERIC(6),
Sname CHAR(20),
Sage NUMERIC(3) NOT NULL,
CONSTRAINT StudentKey PRIMARY KEY(Sno),
CONSTRAINT SnameKey FOREIGN KEY(Sname) REFERENCES Course(Sname),
CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
CONSTRAINT C2 UNIQUE(Sname)
);

修改表中的完整性限制,可以先删除原来的约束条件,再增加新的约束条件

1
2
ALTER TABLE <表名> DROP CONSTRAINT <完整性约束条件名>
ALTER TABLE <表名> ADD CONSTRAINT <完整性约束条件名> <完整性约束条件>

触发器

触发器(trigger)是用户定义在关系表上的一类由事件驱动的特殊过程
触发器类似于约束,但是比约束更加灵活,可以实施更为复杂的检查和操作,具有更精细和更强大的数据控制能力

1
2
3
4
5
6
7
/*触发器一般格式*/
CREATE TRIGGER <触发器名>
[BEFORE|AFTER] [INSERT|DELETE|UPDATE] ON <表名>
FOR EACH [ROW|STATEMENT]
BEGIN
<执行语句列表>
END

例如:建立触发器t1,当插入学生的年龄小于18岁时进行报错提示

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER $   /*告诉mysql语句的结尾符号换成以$结束*/
CREATE TRIGGER t1
BEFORE INSERT ON Student
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(200);
IF NEW.Sage < 18 THEN
SET msg = CONCAT('输入的年龄值: ',NEW.Sage,' 为无效的年龄,请确保学生年龄大于18岁');
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
END IF;
END$
DELIMITER ; /*将结尾符号换回以;结束*/

MySQL 中定义了NEWOLD,用来表示触发器的所在表中,触发了触发器的那一行数据,来引用触发器中发生变化的记录内容,具体地:

  1. INSERT型触发器中,NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据
  2. UPDATE型触发器中,OLD用来表示将要或已经被修改的原数据,NEW用来表示将要或已经修改为的新数据
  3. DELETE型触发器中,OLD用来表示将要或已经被删除的原数据
  4. 另外OLD是只读的,而NEW则可以在触发器中赋值,这样不会再次触发触发器,导致递归循环调用