Lecture 4：数据库完整性

This note is currently available in Chinese. The original wording, formulas and diagrams are preserved to avoid an unverified technical translation.

描述：本节区分数据库完整性与安全性，复习实体、参照和用户定义完整性，并用完整 SQL 示例整理 PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、DEFAULT、CHECK 及命名约束的定义方式。

4.1 数据库完整性

数据库完整性保证数据满足现实语义和业务规则，防止不正确、不相容或无意义的数据进入数据库。

完整性关注：

数据值是否正确。
数据之间是否相容。
数据状态是否满足约束。
更新操作是否把数据库从一个合法状态带到另一个合法状态。

例如：

学号不能重复。
姓名不能为空。
性别值必须来自允许集合。
学生的教师号必须引用现有教师。

4.2 完整性与安全性

课件对比如下：

项目	完整性	安全性
目的	防止不符合语义、不正确的数据存在	防止恶意破坏和非法存取
防范对象	非法数据状态、错误更新	非法用户、越权操作、攻击行为
典型机制	主键、外键、CHECK、触发器	鉴权、授权、加密、审计

两者可能同时作用于一次操作：

安全性先判断用户有没有修改权限。
完整性再判断修改后的数据是否合法。

WARNING “有权修改”不代表“可写入任意值”；权限检查通过后，数据仍必须满足完整性约束。

4.3 三类完整性

4.3.1 实体完整性

实体完整性要求主键能够唯一、稳定地标识每个实体：

主键值唯一。
主键各组成列不得为 NULL。

SQL 使用 PRIMARY KEY 实现。

4.3.2 参照完整性

若表 $R$ 的外键 $F$ 引用表 $S$ 的候选键 $K$，则 $R$ 中每个 $F$ 值必须：

等于 $S$ 中某个现有元组的 $K$ 值；或
在允许空值时取 NULL。

这防止产生“孤儿引用”。

4.3.3 用户定义完整性

用户定义完整性表达具体业务规则，例如：

学号唯一。
姓名非空。
默认年龄为 18。
性别只能取指定值。
分数必须在 0 到 100 之间。

可使用 UNIQUE、NOT NULL、DEFAULT、CHECK、触发器等实现。

4.4 常用 SQL 约束

4.4.1 PRIMARY KEY

CONSTRAINT pk_student PRIMARY KEY (id)

作用：

唯一标识元组。
隐含唯一性。
隐含非空性。
一张表只有一个主键约束，但可由多列组成。

4.4.2 FOREIGN KEY

CONSTRAINT fk_student_teacher
    FOREIGN KEY (tno)
    REFERENCES t_teacher(tno)

作用：

建立引用关系。
限制外键只能为空或匹配被引用键。
可配置删除、更新时的级联策略。

4.4.3 UNIQUE

Sno NUMERIC(6) UNIQUE

保证列或列组不出现重复的非空键值。

UNIQUE 与 PRIMARY KEY 的区别：

维度	PRIMARY KEY	UNIQUE
每表数量	一个约束	可有多个
NULL	不允许	规则因 DBMS 而异
语义	实体主标识	备用唯一性

4.4.4 NOT NULL

Sname CHAR(20) NOT NULL

保证该列必须给出非空值。

4.4.5 DEFAULT

Sage NUMERIC(3) DEFAULT 18

插入时未显式提供该列，系统使用默认值。显式写入 NULL 是否替换为默认值取决于 DBMS 和列定义，不能把 DEFAULT 当成 NOT NULL。

4.4.6 CHECK

Ssex CHAR(2) CHECK (Ssex IN ('男', '女'))

标准 SQL 的 CHECK 采用三值逻辑：检查结果为 FALSE 时拒绝该行，为 TRUE 或 UNKNOWN 时通过。因此本例中 Ssex=NULL 会使条件为 UNKNOWN，单靠 CHECK 不能禁止空值；若必须非空，应同时声明 NOT NULL。

更完整的范围例子：

Score NUMERIC(5, 2)
    NOT NULL
    CHECK (Score BETWEEN 0 AND 100)

4.5 课件建表示例整理

课件第二页的代码被图片边界截断，整理后为：

CREATE TABLE t_teacher (
    tno   CHAR(10) PRIMARY KEY,
    tname CHAR(16)
);

CREATE TABLE t_student (
    id    BIGINT AUTO_INCREMENT,
    Sno   NUMERIC(6) UNIQUE,
    Sname CHAR(20) NOT NULL,
    Sage  NUMERIC(3) DEFAULT 18,
    Ssex  CHAR(2) CHECK (Ssex IN ('男', '女')),
    tno   CHAR(10),

    CONSTRAINT pk_student
        PRIMARY KEY (id),
    CONSTRAINT fk_student_teacher
        FOREIGN KEY (tno)
        REFERENCES t_teacher(tno)
);

约束对应关系：

列/约束	含义
`t_teacher.tno PRIMARY KEY`	教师号唯一且非空
`id AUTO_INCREMENT`	学生内部编号自动增长，语法依 DBMS
`Sno UNIQUE`	学号不重复
`Sname NOT NULL`	姓名必须提供
`Sage DEFAULT 18`	未给年龄时使用 18
`Ssex CHECK (...)`	性别值域限制
`tno FOREIGN KEY`	学生教师号引用教师表

NOTE AUTO_INCREMENT 是 MySQL 风格。其他系统可能使用 IDENTITY、序列或 GENERATED ... AS IDENTITY。

4.6 列级与表级约束

4.6.1 列级定义

约束直接跟在列定义后：

tno CHAR(10) PRIMARY KEY

适合只涉及单列的约束。

4.6.2 表级定义

约束单独写在表定义中：

CONSTRAINT pk_order_detail
    PRIMARY KEY (OrderID, ProductID)

适合：

复合主键。
复合外键。
跨多列的 UNIQUE 或 CHECK。
需要显式命名的约束。

4.6.3 命名约束

一般格式：

CONSTRAINT 约束名 约束短语

显式命名便于：

报错时识别约束。
后续删除或修改约束。
统一数据库对象命名规范。

4.7 主键与索引

主键的逻辑语义是唯一且非空。DBMS 通常会创建或利用唯一索引来高效检查主键，但是否使用聚簇索引属于物理实现策略。

不同系统示例：

MySQL InnoDB 通常按主键组织聚簇索引。
SQL Server 在没有冲突配置时常把主键默认建为聚簇索引，但可以指定非聚簇。
其他 DBMS 可能使用普通唯一索引或不同存储实现。

WARNING · 课件表述纠错 “主键默认有聚簇索引”不是跨 DBMS 的通用关系模型规则。应记住：主键必须唯一且非空；唯一索引/聚簇索引是具体 DBMS 的实现选择。

4.8 约束检查时机

完整性约束会在插入、修改、删除等操作时检查：

插入学生：检查主键、学号、姓名、性别和教师引用。
修改教师号：检查新的教师是否存在。
删除教师：检查是否仍有学生引用，或执行配置的级联动作。
修改分数：检查范围约束。

事务执行失败时，DBMS 通常拒绝违反约束的语句；事务原子性还要保证失败操作不会留下半完成状态。

4.9 易错点

完整性防错误数据，安全性防非法访问和破坏，两者目标不同但相互配合。
PRIMARY KEY 同时保证唯一和非空；UNIQUE 的 NULL 规则依 DBMS 而异。
DEFAULT 只处理缺省输入，不等于非空约束。
外键值必须匹配被引用键或在允许时为空，不要求外键本身唯一。
CHECK 用于限定值域和行内业务条件。
多列约束应使用表级语法，尤其是复合主键和复合外键。
CONSTRAINT 名称便于维护，但不是约束类型本身。
主键的逻辑要求不等于必然采用聚簇索引。
AUTO_INCREMENT 不是标准通用语法，迁移 DBMS 时要替换。

4.10 本节复盘

数据库完整性防止不正确、不相容或不符合业务语义的数据进入数据库。
实体完整性由主键保证，参照完整性由外键保证，用户定义完整性由具体业务约束保证。
PRIMARY KEY 唯一且非空，FOREIGN KEY 维护跨表引用。
UNIQUE 限制重复，NOT NULL 限制空值，DEFAULT 提供缺省值，CHECK 限制条件。
约束可定义在列级或表级；复合约束必须使用表级形式。
视图中的完整 SQL 示例同时覆盖主键、外键、唯一、非空、默认和检查约束。
主键通常借助唯一索引实现，但是否聚簇取决于具体 DBMS，不能当成通用理论规则。