数据库的完整性约束

jiezi

6 年前

列级约束（针对字段，key）

主要针对列的类型，取值范围，精度等约束

- 对空值的约束。规定某个字段是否为空
- 对取值范围的约束。例如，学生成绩的字段规定为 0 - 100
- 数据类型的约束。包括数据类型，长度，精度等。例如常用的定长 varchar
- 数据格式的约束。例如，学生表中的学号 stu_no 字段，认为规定前四位为入学年份，后面是院系的编号等

元组（或称作 row，一条数据）约束
- 元组中字段之间的约束。例如，一个活动的开始时间必须早于它的结束时间
表级约束（外键）
- 指多个元组之间，关系之间的联系的约束。例如，学生成绩表中的 stu_no 字段，实际取值源于学生表中的 stu_no 字段

以上是一些约束的概念，理论上的，如何实现约束，请往下看。

实体的完整性是通过主键（primary key）约束和候选键（candidate key）约束来实现的。所以前提条件是要了解键的一些概念和分类：

key：用于保证元组的唯一性
- super-key：能够区分唯一的元组的集合
- candidate key：super-key 中最小集
- primary key：candidate key 中人工选择一个（一张表只能有一个或多个组成的联合主键）

举个例子：例如有 students 表，含有字段 stu_number（学号）id（身份证号）name（姓名) 那么找出所有能够保证元组唯一性的 super-key={{stu_number}, {id}, {stu_number, name}, {id, name}, {stu_number, id}, {stu_number, id, name}} 然后可得 candidate key=stu_number 或 id

主键约束
- 每张表只能定义一个主键或多个主键组合的联合主键（复合主键）
- 确保能够根据主键查询到唯一的元组，且不能为 NULL，这是唯一性原则
- 联合主键不能包含不必要的字段。也就是说，从联合主键中删除其中一列后，还能保证唯一性，那么是不正确的。因为要满足最小集原则
- 一个字段只能在联合主键中出现一次。因为集合的元素是唯一的
创建主键约束
- 可以在 CREATE TABLE 或 ALTER TABLE 语句中使用 PRIMARY KEY 来实现
- 唯一主键：直接在某个字段后加上关键字
- 联合主键：PRIMARY KEY(column_0, column_1, …)

创建主键后，数据库会自动创建唯一索引，用于对主键的快速查询，索引名默认为 PRIMARY，也可以重新自定义命名

创建候选键索引
- 可以在 CREATE TABLE 或 ALTER TABLE 语句中使用 UNIQUE 来实现
- 主键和候选键一样，只不过主键是唯一的，候选键可以是多个，所以同样具有唯一性，且不能为 NULL

创建候选键后，数据库也会自动创建 UNIQUE 索引

可以在 CREATE TABLE 或 ALTER TABLE 语句中使用 FOREIGN KEY 和 REFERENCES 来实现

FOREIGN KEY(column_name, ...)
        REFERENCES table_name(column_name, ...)
        [ON DELETE reference_option] 
        [ON UPDATE reference_option] 
#column_name 的语法格式为：column_name [(length)] [ASC | DESC]
#refrence_option 提供选项有：RESTRICT | CASCADE | SET NULL | NO ACTION

关键字 ON DELETE 和ON UPDATE 是指定参照的动作，只要涉及这两个动作，系统就会检查完整性约束，即refrence_option，如果没有明确指定，那么其默认值为RESTRICT
- RESTRICT：拒绝操作，当删除或更新被参照表时涉及到了被参照的字段，并且该字段在外键中存在数据，那么系统将拒绝操作
- CASCADE：级联操作，当删除或更新被参照表时，会同时删除或更新参照表中的匹配的元组
- SET NULL：置空操作，当删除或更新被参照表时，会将参照表中对应的外键字段的值设为NULL，前提是该字段在参照表中没有声明NOT NULL
- NO ACTION：不做操作，和 RESTRICT 相同

参照表：声明了 ```FOREIGN KEY``` 的表 A。被参照表：主键被表 A 声明为 ```FOREIGN KEY```

举个例子：学生有选历史课，但是现在学校决定不开设历史课了，要把历史课从课程表删除，但是删除的时候会检查有没有被引用，如果存在引用则删除失败，这就是 ```RESTRICT```。如果删除历史课的同时，将所有学生选课表中的历史课也删除，这就是 ```CASCADE```。如果删除了历史课，只是将选课表中的历史课设为 ```NULL```，这就是 ```SET NULL```，万一哪天又恢复历史课呢????

关键字
- CHECK：在更新或删除时，检查数据是否满足条件
- NOT NULL：比较常用，定义一个字段不可为空。可以理解为 CHECK(column NOT NULL)
TRIGGER（触发器）
- 触发器是由事件驱动的，主要用于保护主键和外键的参照完整性和数据一致性
- 无法主动调用，可理解为数据库系统维护了一个线程或一组线程去轮询加了触发器的事件

可以使用 CREATE TRIGGER 创建触发器，其格式为：```sql
CREATE TRIGGER trigger_name trigger_time trigger_event
    ON table_name FOR EACH ROW trigger_body
 # trigger_name：触发器唯一命名
 # trigger_body：触发器具体的执行逻辑
 # trigger_event：事件类型，INSERT DELETE UPDATE
 # trigger_time：触发时间，参照的是 trigger_event 事件发生时间，AFTER(常用) BEFORE

综上，我们简单的总结下

约束：向表中插入数据时，数据库会判断数据是否符合约束，不符合则插入失败

CHECK：用于约束范围例如 CHECK(score>60)
DEFAULT：字段没有值的话，给定一个默认值
UNIQUE：约束 row 的唯一性，可以建立多个
PRIMARY KEY：约束 row 的唯一性，只能建立一个
NOT NULL：约束强制 column (字段或直列)不接受 NULL 值，且字段始终包含值
FOREIGN KEY：A 表与 B 表中同名的字段，且是 B 表的 PRIMARY KEY（或者说 A 表的 FOREIGN KEY 指向 B 表中的 PRIMARY KEY）

参考：

《数据结构系统原理》（黄靖）

数据库的完整性约束

完整性约束条件的作用对象：

实体完整性

参照完整性（reference 或称作引用）

用户定义的完整性