Star 的档案库

Appearance

数据库完整性 ​

概述 ​

什么是数据库完整性 ​

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

  • 正确性：数据符合现实世界语义，反映当前实际状况
  • 相容性：数据库同一对象在不同关系表中的数据符合逻辑

完整性约束的目的是防止不合语义的数据进入数据库。

例： 学生的年龄必须是整数，取值范围 14~29；性别只能是男或女；学号唯一；所在系必须是学校已成立的系。

完整性与安全性的区别 ​

概念防范对象目的
数据完整性不合语义的、不正确的数据防止数据库中存在不符合语义的数据
数据安全性非法用户和非法操作防止恶意的破坏和非法的存取

完整性控制机制的三个组成部分 ​

  1. 提供定义完整性约束条件的机制

    • 完整性约束条件（规则）是数据库数据必须满足的语义约束
    • SQL 标准使用实体完整性、参照完整性和用户定义完整性来描述
    • 一般由 SQL 的 DDL 语句实现

  2. 提供完整性检查的方法

    • 在 INSERT、UPDATE、DELETE 语句执行后开始检查，也可在事务提交时检查

  3. 违约处理

    • 拒绝（NO ACTION） 执行该操作
    • 级联（CASCADE） 执行其他操作

5.1 实体完整性 ​

5.1.1 实体完整性定义 ​

关系模型的实体完整性通过 CREATE TABLE 中的 PRIMARY KEY 定义。

  • 单属性构成的码：有两种定义方式
    • 列级约束条件
    • 表级约束条件
  • 多属性构成的码：只有一种定义方式
    • 表级约束条件

例 1：将 Student 表的 Sno 属性定义为码 ​

（1）列级定义主码：

sql
CREATE TABLE Student
(  Sno   CHAR(9) PRIMARY KEY,
   Sname CHAR(20) NOT NULL,
   Ssex  CHAR(2),
   Sage  SMALLINT,
   Sdept CHAR(20)
);

（2）表级定义主码：

sql
CREATE TABLE Student
(  Sno   CHAR(9),
   Sname CHAR(20) NOT NULL,
   Ssex  CHAR(2),
   Sage  SMALLINT,
   Sdept CHAR(20),
   PRIMARY KEY (Sno)
);

例 2：将 SC 表的 (Sno, Cno) 属性组定义为码 ​

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

5.1.2 实体完整性检查和违约处理 ​

对主码列进行插入或更新操作时，RDBMS 自动检查：

  1. 主码值是否唯一——不唯一则拒绝
  2. 主码各个属性是否为空——任一为空则拒绝

唯一性检查方法 ​

  • 全表扫描：逐条比较主码值（十分耗时）
  • 索引优化：RDBMS 核心一般在主码上自动建立索引（如 B+ 树索引），避免全表扫描

5.2 参照完整性 ​

5.2.1 参照完整性定义 ​

在 CREATE TABLE 中：

  • 用 FOREIGN KEY 短语定义哪些列为外码
  • 用 REFERENCES 短语指明这些外码参照哪些表的主码

例 3：定义 SC 中的参照完整性 ​

sql
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 Course(Cno)   /* 表级定义参照完整性 */
);

5.2.2 参照完整性检查和违约处理 ​

对被参照表和参照表进行更新操作时可能破坏参照完整性，必须进行检查。违约处理策略：

策略说明
NO ACTION（拒绝）默认策略，拒绝执行破坏参照完整性的操作
CASCADE（级联）连带更新或删除参照表中相应的元组
SET-NULL（设置为空值）将外码列设置为空值（须先定义外码列允许空值）

例 4：显式说明参照完整性的违约处理 ​

sql
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 中元组造成不一致时拒绝 */
      ON UPDATE CASCADE      /* 更新 Course 中 Cno 时级联更新 SC 表 */
);

5.3 用户定义的完整性 ​

用户定义的完整性是针对某一具体应用的数据必须满足的语义要求，由 RDBMS 提供，不必由应用程序承担。

5.3.1 属性上的约束条件定义 ​

在 CREATE TABLE 时定义：

  1. 列值非空（NOT NULL）
  2. 列值唯一（UNIQUE）
  3. 检查列值是否满足条件表达式（CHECK）

例 5：不允许取空值 ​

sql
CREATE TABLE SC
(  Sno   CHAR(9) NOT NULL,
   Cno   CHAR(4) NOT NULL,
   Grade SMALLINT NOT NULL,
   PRIMARY KEY (Sno, Cno),
   ...
);

注：如果在表级定义了实体完整性（PRIMARY KEY），隐含 Sno、Cno 不允许为空，则列级 NOT NULL 定义可省略。

例 6：列值唯一 ​

sql
CREATE TABLE DEPT
(  Deptno   NUMERIC(2),
   Dname    CHAR(9) UNIQUE,   /* Dname 列值唯一 */
   Location CHAR(10),
   PRIMARY KEY (Deptno)
);

例 7：用 CHECK 指定列值条件 ​

sql
CREATE TABLE Student
(  Sno   CHAR(9) PRIMARY KEY,
   Sname CHAR(8) NOT NULL,
   Ssex  CHAR(2) CHECK (Ssex IN ('男', '女')),  /* 性别只能取'男'或'女' */
   Sage  SMALLINT,
   Sdept CHAR(20)
);

5.3.2 属性上的约束条件检查和违约处理 ​

插入元组或修改属性的值时，RDBMS 检查属性上的约束条件是否满足，不满足则拒绝执行。

5.3.3 元组上的约束条件定义 ​

使用 CHECK 短语定义元组级约束，可以设置不同属性之间取值的相互约束。

例 9：性别为男时名字不能以 "Ms." 开头 ​

sql
CREATE TABLE Student
(  Sno   CHAR(9),
   Sname CHAR(8) NOT NULL,
   Ssex  CHAR(2),
   Sage  SMALLINT,
   Sdept CHAR(20),
   PRIMARY KEY (Sno),
   CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
   /* 元组中 Sname 和 Ssex 两个属性值之间的约束 */
);

5.3.4 元组上的约束条件检查和违约处理 ​

插入或修改时检查，不满足则拒绝。

5.4 完整性约束命名子句 ​

1. CONSTRAINT 约束 ​

sql
CONSTRAINT <完整性约束条件名>
   [ PRIMARY KEY 短语
   | FOREIGN KEY 短语
   | CHECK 短语
   | NOT NULL
   | UNIQUE ]

例 10：建立 Student 表，命名各约束 ​

sql
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)
);

建立了 5 个约束：主码约束 StudentKey，以及 C1、C2、C3、C4 四个列级约束。

2. 修改表中的完整性限制 ​

使用 ALTER TABLE 语句修改。先删除原约束，再添加新约束。

例 13：修改约束条件 ​

sql
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.5 域中的完整性限制 ​

SQL 支持域的概念，使用 CREATE DOMAIN 语句建立一个域及其完整性约束。

例 14：建立性别域 ​

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

/* 使用域 */
Ssex GenderDomain

例 15：对域中的限制命名 ​

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

例 16：删除域的限制条件 ​

sql
ALTER DOMAIN GenderDomain DROP CONSTRAINT GD;

例 17：增加域的限制条件 ​

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

通过例 16 和例 17，性别的取值范围由（'男', '女'）改为（'1', '0'）。

5.6 断言 ​

断言（Assertion）使用 CREATE ASSERTION 语句，可定义涉及多个表或聚集操作的更复杂的完整性约束。

特点：断言创建后，任何对断言中所涉及关系的操作都会触发 RDBMS 检查，使断言不为真值的操作被拒绝。

创建断言 ​

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

例 18：限制数据库课程最多 60 名学生选修 ​

sql
CREATE ASSERTION ASSE_SC_DB_NUM
CHECK (60 >= (
   SELECT COUNT(*)
   FROM Course, SC
   WHERE SC.Cno = Course.Cno AND Course.Cname = '数据库'
));

例 19：限制每一门课程最多 60 名学生选修 ​

sql
CREATE ASSERTION ASSE_SC_CNUM1
CHECK (60 >= ALL (
   SELECT COUNT(*)
   FROM SC
   GROUP BY Cno
));

例 20：限制每个学期每一门课程最多 60 名学生选修 ​

先修改 SC 表模式，增加学期属性：

sql
ALTER TABLE SC ADD TERM DATE;

然后定义断言：

sql
CREATE ASSERTION ASSE_SC_CNUM2
CHECK (60 >= ALL (
   SELECT COUNT(*)
   FROM SC
   GROUP BY Cno, TERM
));

删除断言 ​

sql
DROP ASSERTION <断言名>;

注意：复杂的断言会导致较高的系统检测和维护开销。

5.7 触发器 ​

触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程，保存在数据库服务器中。任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，可以进行更复杂、更精细的检查和操作。

5.7.1 定义触发器 ​

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

触发器又叫做 事件-条件-动作（ECA）规则。

语法说明 ​

组成说明
创建者表的拥有者
触发器名同一模式下唯一，与表名在同一模式下
表名触发器的目标表，只能定义在基本表上
触发事件INSERT、DELETE、UPDATE 或其组合，可加 UPDATE OF <列> 指定列
触发时机BEFORE（操作前激活）或 AFTER（操作后激活）
触发器类型FOR EACH ROW（行级）或 FOR EACH STATEMENT（语句级）
触发条件WHEN 子句，条件为真时执行触发动作体，省略则立即执行
触发动作体匿名 PL/SQL 过程块或调用已创建的存储过程

行级 vs 语句级触发器：例如 TEACHER 表有 1000 行，执行 UPDATE TEACHER SET Deptno=5;：

  • 语句级触发器 → 触发动作只执行 1 次
  • 行级触发器 → 触发动作执行 1000 次

行级触发器中可使用 NEW 和 OLD 引用事件前后的值；语句级触发器不可使用。

例 21：AFTER 行级触发器——记录 Grade 修改 ​

当对 SC 表的 Grade 属性进行修改，若分数增加 ≥10%，将操作记录到 SC_U(Sno, Cno, Oldgrade, Newgrade) 表中：

sql
CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
REFERENCING
   OLD ROW AS OldTuple,
   NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.Grade >= 1.1 * OldTuple.Grade)
   INSERT INTO SC_U(Sno, Cno, OldGrade, NewGrade)
   VALUES (OldTuple.Sno, OldTuple.Cno, OldTuple.Grade, NewTuple.Grade);

例 22：AFTER 语句级触发器——记录插入学生个数 ​

sql
CREATE TRIGGER Student_Count
AFTER INSERT ON Student
REFERENCING
   NEW TABLE AS DELTA
FOR EACH STATEMENT
   INSERT INTO StudentInsertLog (Numbers)
   SELECT COUNT(*) FROM DELTA;

例 23：BEFORE 行级触发器——工资完整性约束 ​

教授的工资不得低于 4000 元，如果低于 4000 元，自动改为 4000 元：

sql
CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
FOR EACH ROW
AS BEGIN
   IF (new.Job = '教授') AND (new.Sal < 4000) THEN
      new.Sal := 4000;
   END IF;
END;

5.7.2 触发器的激活 ​

由触发事件激活，数据库服务器自动执行。同一表上多个触发器的执行顺序：

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

5.7.3 删除触发器 ​

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

触发器必须已经创建，且只能由具有相应权限的用户删除。

注意：不同 RDBMS 产品的触发器语法各不相同。

本章小结 ​

本章围绕数据库完整性展开，重点说明如何通过约束、断言和触发器保证数据的正确性与相容性。

关键内容 ​

  • 完整性概念：完整性用于防止不合语义的数据进入数据库，与防止非法访问的安全性不同。
  • 完整性控制机制：RDBMS 需要提供约束定义机制、完整性检查机制和违约处理机制。
  • 实体完整性：通过 PRIMARY KEY 定义，保证主码唯一且非空。
  • 参照完整性：通过 FOREIGN KEY ... REFERENCES 定义，保证外码值为空或匹配被参照关系的主码。
  • 用户定义完整性：通过 NOT NULL、UNIQUE、CHECK 等约束表达具体业务语义。
  • 复杂完整性规则：可使用约束命名、域约束、断言和触发器定义更复杂的完整性条件。

核心要点 ​

  • 完整性检查通常在插入、修改、删除后执行，违约处理包括拒绝执行和级联操作。
  • CONSTRAINT 为约束命名后，便于后续修改和删除。
  • 触发器适合表达事件驱动、跨表或过程化的复杂完整性规则，但不同 RDBMS 语法差异较大。

关键术语 ​

数据库完整性, 实体完整性, 参照完整性, 用户定义完整性, PRIMARY KEY, FOREIGN KEY, REFERENCES, NOT NULL, UNIQUE, CHECK, CONSTRAINT, DOMAIN, ASSERTION, TRIGGER。