第5章 数据库完整性

数据库管理系统中检查数据是否满足完整性约束条件的机制称为完整性检查

① 静态约束: 要求数据库在任何时候都要满足的约束 ② 动态动态: 在数据库改变状态时要满足的约束

• 静态约束
  • 与表有关的约束，域约束
  • 断言（请谨慎使用）
• 动态约束：触发器
  • 触发器可以细分引起完整性约束检查的条件
  • 触发器不仅可以检查数据完整性，还可以进行数据处理、实现某些业务处理逻辑

1 实体完整性

• 在 CREATE TABLE 命令中用 PRIMARY KEY 定义主码
• 或者用 UNIQUE + NOT NULL 定义唯一码

-- 在列级定义主码
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,
  Sname CHAR(20) NOT NULL,
  Ssex CHAR(2),
  Sage SMALLINT,
  Sdept CHAR(20)
);

-- 在表级定义主码
CREATE TABLE Student (
  Sno CHAR(9),
  Sname CHAR(20) NOT NULL,
  Ssex CHAR(2),
  Sage SMALLINT,
  Sdept CHAR(20),
  PRIMARY KEY (Sno)
);

CREATE TABLE SC (
  Sno CHAR(9) NOT NULL,
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT,
  PRIMARY KEY (Sno, Cno) /*只能在表级定义主码*/
);

2 参照完整性

在 CREATE TABLE 命令中用 FOREIGN KEY 短语定义外码，用 REFERENCES 短语指明这些外码参照哪些表的主码

CREATE TABLE SC (
  Sno CHAR(9) NOT NULL REFERENCES Student(Sno),
  /* 在列级定义参照完整性- 外码Sno */
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT,
  PRIMARY KEY (Sno, Cno),
  /* 在表级定义实体完整性 - 主码(Sno,Cno) */
  FOREIGN KEY (Cno) REFERENCES Course(Cno)
  /* 在表级定义参照完整性 - 外码 Cno */
);

• SC 为参照表
• Student 为被参照表

参照完整性违约处理

• 拒绝（NO ACTION）执行
  • 不允许该操作执行。该策略一般设置为默认策略
• 级联（CASCADE）操作
  • 当删除或修改被参照表(Student)的一个元组造成了与参照表(SC)的不一致，则删除或修改参照表中的所有造成不一致的元组
• 设置为空值（SET-NULL）
  • 当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应列设置为空值
  • 对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值

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)
    ON DELETE CASCADE  /*级联删除SC表中相应的元组*/
    ON UPDATE CASCADE, /*级联更新SC表中相应的元组*/
  FOREIGN KEY (Cno) REFERENCES Course(Cno)
    ON DELETE NO ACTION /*当删除course表中的元组造成了与SC表不一致时拒绝删除*/
    ON UPDATE CASCADE /*当更新course表中的cno时，级联更新SC表中相应的元组*/
);

3 用户定义的完整性

• 列上的约束条件
  • 列值非空：NOT NULL
  • 列值唯一：UNIQUE
  • 定义列值应该满足的条件：CHECK <条件表达式>
    • <条件表达式>仅涉及到当前的单个列
• 元组上的约束条件
  • 列值唯一：UNIQUE(<列名> [, <列名>]...)
  • 定义列值应该满足的条件：CHECK <条件表达式>
    • <条件表达式>可能涉及单个或多个列

CREATE TABLE DEPT (
  Deptno NUMERIC(2),
  Dname CHAR(9) UNIQUE NOT NULL，
  /* 要求Dname列值唯一, 并且显式定义NOT NULL约束*/
  Location CHAR(10),
  PRIMARY KEY (Deptno) /* 主键隐含着部门编号不能取空值*/
);

CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,
  Sname CHAR(8) NOT NULL,
  Ssex CHAR(2) CHECK (Ssex IN ('男','女')),
  /* 性别列Ssex只允许取'男' 或'女' */
  Sage SMALLINT,
  Grade SMALLINT CHECK (Grade>=0 AND Grade <=100)，
  Sdept CHAR(20)
);

4 完整性约束命名字句

完整性约束的定义与修改

• 在 CREATE TABLE 语句中定义完整性约束
• 在定义完整性约束时，可以对完整性约束进行命名
  • CONSTRAINT <完整性约束条件名> <完整性约束条件>
• 使用 ALTER TABLE 语句来修改表中的完整性约束的定义

CREATE TABLE Student (
  Sno NUMERIC(6) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
  Sname CHAR(20) CONSTRAINT C2 NOT NULL,
  Sage NUMERIC(3) CONSTRAINT C3 CHECK (Sage < 30),
  Ssex CHAR(2) CONSTRAINT C4 CHECK (Ssex IN('男','女')),
  CONSTRAINT StudentKey PRIMARY KEY(Sno) # 主码约束命名
);

-- 去掉性别限制
ALTER TABLE Student DROP CONSTRAINT C4; 

-- 学号改为在 900000~999999 之间，年龄由小于 30 改为小于 40
ALTER TABLE Student DROP CONSTRAINT C1;

ALTER TABLE Student
ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999);

ALTER TABLE Student DROP CONSTRAINT C3;

ALTER TABLE Student ADD CONSTRAINT C3 CHECK(Sage < 40);

5 域中的完整性限制

域：一组具有相同数据类型的值的集合

• 可以用 CREATE DOMAIN 命令来创建新的域（用户自定义数据类型）
• 在用 CREATE DOMAIN 语句定义域时，可以定义域上的完整性约束，即该域中的值应该满足的约束条件
• 可以方便对所有使用相同‘域’的列的完整性约束的统一定义

CREATE DOMAIN GenderDomain CHAR(2)
CONSTRAINT GD CHECK(VALUE IN ('男','女'));

ALTER DOMAIN GenderDomain DROP CONSTRAINT GD;

ALTER DOMAIN GenderDomain
ADD CONSTRAINT GDD CHECK(VALUE IN('1','0'));

6 断言

CREATE ASSERTION <断言名> <CHECK子句>;

DROP ASSERTION <断言名>;

断言创建以后，任何对断言中所涉及的关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真值的操作都会被拒绝执行

【例】 限制数据库课程最多 60 名学生选修

CREATE ASSERTION ASSE_SC_DB_NUM
CHECK ( 60 >= ( Select count(*) /* 此断言的谓词涉及聚集操作count */
                From Course, SC
                Where SC.Cno = Course.Cno 
                  and Course.Cname ='数据库')
);

CHECK 中搭配 NOT EXISTS 可解决很多复杂约束要求

7 触发器

• 触发器保存在数据库服务器中
• 任何用户对表的增、删、改操作均由服务器自动激活相应的触发器
• 触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力

CREATE TRIGGER <触发器名>
{ BEFORE | AFTER } <触发事件> ON <表名>
REFERENCING NEW | OLD ROW AS <变量>
FOR EACH { ROW | STATEMENT }
[ WHEN <触发条件>] <触发动作体>

• 表的拥有者才可以在表上创建触发器
• 触发器只能定义在基本表上，不能定义在视图上

【例】当对表 SC 的 Grade 列进行修改时，若分数增加了 10% 则将此次操作记录到表 SC_U(Sno,Cno,Oldgrade,Newgrade) 中去。其中：Oldgrade 是修改前的分数，Newgrade 是修改后的分数。

CREATE TRIGGER SC_T /* 触发器名称 */
AFTER UPDATE OF Grade ON SC /* 定义触发事件, 及其与结果事件的执行顺序*/
REFERENCING OLD row AS OldTuple, NEW row AS NewTuple
  /*定义对新旧元组的引用名,缺省情况下,可以直接用NEW和OLD来引用新旧元组*/
FOR EACH ROW /*行级触发器，每UPDATE一条元组，结果事件被执行一次*/
WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade)
INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade) /*该INSERT语句就是结果事件*/
VALUES(OldTuple.Sno, OldTuple.Cno, OldTuple.Grade, NewTuple.Grade);

【例】将每次对表 Student 的插入操作所增加的学生人数记录到表 StudentInsertLog 中

CREATE TRIGGER Student_Count
AFTER INSERT ON Student /* 指明触发器激活的时间是在执行INSERT后*/
REFERENCING NEW TABLE AS DELTA
  /* 为执行INSERT操作前的student表定义一个引用名DELTA*/
FOR EACH STATEMENT
  /* 语句级触发器, 即执行完INSERT语句后, 对应的触发动作体只执行一次*/
INSERT INTO StudentInsertLog (Numbers)
SELECT COUNT(*) FROM DELTA;

一个数据表上可能定义了多个触发器，遵循如下的执行顺序：

1. 执行该表上的 BEFORE 触发器
2. 激活触发器的 SQL 语句
3. 执行该表上的 AFTER 触发器

删除触发器的 SQL 语法

DROP TRIGGER <触发器名> ON <表名>;