1. 概述
数据库的完整性指数据的正确性(correctness)和相容性(compat-ability)。
- 正确性:指数据符合现实语义。
- 相容性:指同一对象在不同关系表中的数据是符合逻辑的。
为了维护数据库的完整性,数据库管理系统(DBMS)必须实现如下功能:
-
定义完整性约束
在SQL标准中定义了一系列定义完整性约束的语句。 -
完整性检查
检查数据是否符合完整性约束条件的机制成为完整性检查。完整性检查通常在INSERT、UPDATE、DELETE语句执行后开始检查,也可在事务提交时检查。 -
违约处理
在DBMS发现用户的操作违背了完整性约束条件,将采取一定的操作。
关系数据库管理系统使得完整性控制成为其核心支持的功能,从而能为所有用户和应用提供一致的数据库完整性。
数据库完整性主要分为:实体完整性、参照完整性和用户定义完整性。
在下文中,我将逐一介绍上述三类完整性,并且还会介绍一些SQL中定义的一些特性。
2. 实体完整性
定义实体完整性
示例:
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, -- 在列一级定义主码
Sname CHAR(20),
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
PRIMARY KEY(Sno) -- 在表一级定义主码
);
实体完整性检查和违约处理
使用PRIMARY KEY
短语定义关系的主码后。每当用户插入或更新记录时,DBMS都会自动进行实体完整性检查:
- 检查主码是否唯一,若不唯一便拒绝插入或修改。
- 检查主码的各个属性是否为空,若存在为空便拒绝插入或修改。
3. 参照完整性
关系模型的参照完整性在创建表时使用FOREIGN KEY
短语定义参照关系。
定义参照完整性
示例:
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno), -- 定义表级参照完整性
FOREIGN KEY (Cno) REFERENCES Student(Cno) -- 定义表级参照完整性
);
参照完整性检查
如你所知,参照完整性将两个表中相应的元组联系起来。因而在对参照表和被参照表进行增、删、改操作时有可能破坏参照完整性。
- 在参照表中:插入元组或修改外码可能导致参照完整性被破坏。
- 在被参照表中,删除元组或修改主码可能导致参照完整性被破坏。
违约处理
当上述情况发生时,系统将采取下列处理:
- 拒绝执行(NO ACTION):默认策略。
- 级联操作(CASCADE):修改或删除所有导致不一致的元组。
- 设为空值(NULL):删除或修改被参照表中的元组而造成不一致时,将参照表中所有造成不一致的元组的相应属性设为空值。
4. 用户定义完整性
属性上的完整性约束
分为三类:
- 列值非空(NOT NULL)
- 列值唯一(UNIQUE)
- 检查列值是否满足一个条件表达式(CHECK语句)
列值非空示例:
CREATE TABLE Student
(Sno CHAR(9),
Sname CHAR(20) NOT NULL, -- 定义列值非空
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
列值唯一示例:
CREATE TABLE dept
(Deptno NUMERIC(2),
Dname CHAR(9) UNIQUE
Location CHAR(10)
PRIMARY KEY(Deptno))
检查列值条件:
CREATE TABLE Score
(Sno CHAR(10),
Cno CHAR(20),
Grade SMALLINT CHECK (Grade>=0 AND Grade <= 100)
);
元组上的完整性约束
CREATE TABLE WStudent
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20),
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
CHECK(Ssex='女' AND Sage BETWEEN 18 AND 22) -- 元组级完整性约束
);
完整性约束检查和违约处理
在向表中插入元组或修改属性的值时,DBMS将检查属性和元组上的约束条件是否被满足,如果不满足该操作将被拒绝执行。
5. 完整性约束命名
SQL提供了完整性约束命名子句CONSTRAINT
,用来对完整性约束条件命名,从而可以灵活地增加、删除一个完整性约束。
完整性约束命名
在创建关系表时定义完整性约束命名。
CREATE TABLE Student
(Sno NUMERIC(6)
CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
Sname CHAR(20)
CONSTRAINT C2 NOT NULL); -- 为约束条件命名
修改完整性约束
使用ALTER TABLE
语句修改表中的完整性限制。
ALTER TABLE Student
DROP CONSTRAINT C1; -- 删除约束
ALTER TABLE Student
ADD CONSTRAINT C3 CHECK (Sage < 40); -- 新增约束
6. 断言
SQL中可以使用CREATE ASSERTION
语句来定义断言。通过断言可以来指定更具一般性的约束。
在断言创立后,任何对断言中所涉及的关系的操作都会触发DBMS对断言的检查,任何使断言不为真值的操作都会被拒绝执行。
创建断言
CREATE ASSERTION <断言名> <CHECK子句>
示例,限制一门课最多60个学生选修:
CREATE ASSERTION ASSE_SC_CNUM2
CHECK (60 >= ALL(SELECT COUNT(*) FROM SC GROUP BY cno, term))
删除断言
DROP ASSERTION <断言名>
7. 触发器
触发器(Trigger)使用户定义在关系表上的一类由事件驱动的特殊过程。
触发器类似于约束,但是比约束更灵活,可以实施更为复杂的检查和操作,具有更精细和更强大的数据控制能力。
定义触发器
触发器又称事件-条件-动作规则。
CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS <变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>] <触发动作>
说明:
- 只有表的创建者才能在表上创建触发器。
- 激活时机
AFTER表示在触发操作执行之后激活触发器,BEFORE表示在触发操作执行之前激活触发器。 - 触发器类型
按所触发动作的间隔尺寸可以分为行级(ROW)触发器和语句级(STATEMENT)触发器。 - 触发条件
触发器被激活时,只有当触发条件为真时触发动作才执行,否则触发动作不执行。如果省略WHEN触发条件,则触发动作体在触发器激活后立即执行。
激活触发器
对于同一个表上的多个触发器,遵循”先创建的先执行“原则,即按触发器创建的是时间先后顺序执行。但具体执行顺序依所使用的DBMS而定。
删除触发器
DROP TRIGGER <触发器名> ON <表名>
小结
- 本文讲解了关系数据库的完整性约束机制,包括完整性约束定义、完整性约束检查和违规处理。
- 数据库完整性定义一般由SQL的数据定义语言来实现。他们作为数据库模式的一部分存入数据字典中,在数据被修改时,DBMS的完整性检查机制将按照数据字典中定义的这些约束进行检查。
- 完整性约束机制也会影响系统的性能。
- 触发器是实现数据库完整性的一个重要方法,触发器比完整性约束控制更灵活,功能更强。