5.数据库完整性

概述

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

正确性：数据是否符合现实世界的语义，反映了当前实际情况

相容性：数据库同一对象在不同关系表中的数据时符合逻辑的

​ 完整性是指数据从逻辑上看是否正确

​ 比如学号必须唯一，性别只有男或女，学生所选的课程必须是学校有的课程

数据库在完整性应该具备的功能

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

​ 完整性约束是数据库中数据必须满足的语义约束条件

​ SQL通过数据定义语言描述完整性，包括实体完整性，参照完整性和用户定义完整性

（2）提供完整性检查的方法

​ DBMS提供检查数据是否满足完整性约束的机制

​ 一般在INSERT，UPDATE，DELETE语句执行后检查完整性，也可以在提交事务时检查

（3）违约处理

​ 如果用户的操作违背了完整性约束，就拒绝执行该操作

​ 或者级联执行其他工作等方式保证完整性

三大类完整性

实体完整性

1.关系模型的实体完整性：

​ 即要设置主码，让每条记录是相互可区分的，SQL中在CREATE TABLE中用PRIMARY KEY 定义

​ 主码可以是单个，称为单属性；也可以是多个属性组合，称为多属性

主码是单属性时，可以定义为：

• 定义为列级约束条件
• 定义为表级约束条件

主码是多属性时，只能定义为表级约束条件

例：将Student表中的Sno属性定义为主码

(1)在列级定义主码(在列后标记主码)

​ CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY,

​ SNAME CHAR (20),

​ );

(2)在表级定义主码(在所有列后指定主码)

​ CREATE TABLE Student(Sno CHAR(9)

​ SNAME CHAR (20),

​ PRIMARY KET(Sno)

​ );

(3)多属性只能在表级定义主码

​ CREATE TABLE SC(Sno CHAR(9)

​ Cno CHAR(4),

​ PRIMARY KET(Sno,Cno)

​ );

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

完整性检测的内容

​ 关系数据库管理系统按照实体完整性规则自动进行检查，检查内容包括：

• 检查主码是否唯一，如果不唯一则拒绝插入或修改
• 检查主码各个属性是否为空，只要有一个为空就拒绝插入或修改

检查主码值的方法

​ 检查记录中的主码值是否唯一，一种方法是进行全局扫描，将插入记录和表中的每一条记录的主码进行比较

​ 但是全局扫描十分耗时，RDBMS核心在主码上会自动建立一个索引，例如B+树索引，减少扫描的数量

参照完整性

​ 定义：在CREATE TABLE 中用FOREIGN KEY 定义那些列为外码，用REFERENCES短语指明这些外码参照那些表的主码

​ 例如SC表中Sno，Cno是主码，Sno，Cno分别为Student表的主码和Course表的主码

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

CREATE TABLE SC(

​ Sno CHAR(9) NOT NULL,

​ Cno CHAR(4) NOT NULL,

​ PRIMARY KEY (Sno,Cno),/ 实体完整性*/*

​ FOREIGN KEY (Sno) REFERENCES Student(Sno)

​ FOREIGN KEY (Cno) REFERENCES Course(Cno)

​ / 在表级定义参照完整性*/*

);

定义好外码后，SC表中有的Sno,Student表中一定有

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

​ 参照完整性将两个表中的相应元组连接起来，所以对被参照表和参照表进行增删改操作时都有可能破坏参照完整性，必须进行完整性检查

​ 以Student表和SC表的Sno举例，有四种可能破坏参照完整性的情况

情况	违约处理
SC表插入元组，Sno是Student表中没有的	拒绝
SC表修改Sno，Sno是Student表中没有的	拒绝
Student表删除元组，Sno是SC已有的	拒绝/级联删除/设置为空值
Student表修改Sno，Sno是SC已有的	拒绝/级联删除/设置为空值

违约处理规则

1.拒绝

​ 不允许该操作执行。该策略一般为默认策略

2.级联(CASCADE)操作

​ 删除或修改被参照表时，若造成了与参照表的不一致，则删除或修改所有造成不一致的元组

​ 改变所有相关的元组

3.设置为空值

​ 删除或修改被参照表时，若造成了与参照表的不一致，则将所有不一致的元组对应属性设置为空值

例1：有下面两个关系

学生（学号，专业号，姓名）

专业（专业号，专业名）

则专业号属于外码

​ 假设将专业表中专业号为12的元组删除：

• 按照级联操作策略：删除学生中所有专业号为12的元组
• 设置为空值策略：设置学生中所有专业号为12的元组专业号为空值

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

​ CREATE TABLE SC(

​ Sno CHAR(9) NOT NULL,

​ Cno CHAR(4) NOT NULL,

​ PRIMAEY KEY(Sno,Cno)

​ FOREIGN KEY (Sno) REFERENCES Student(Sno)

​ ON DELETE CASCADE/ 级联删除SC表中相应的元组*/*

​ ON UPDATE CASCADE/ 级联更新SC表中相应的元组*/*

​ FOREIGN KEY(Cno) REDERENCES Course(Cno)

​ ON DELETE NO ACTION

​ / 当删除Course表中的元组造成了与SC表不一致时拒绝删除*/*

​ ON UPDATE CASCADE

)

用户定义的完整性

属性上的约束条件

CREATE TABLE 时，定义属性上的约束条件包括

列值非空 NOT NULL

列值唯一 UNIQUE

满足列支是否满足一个条件表达式 CHECK

一个列可以有多个约束条件

例：Student表中的Ssex只允许取"男"或"女"

CREATE TABLE Student(

​ Sno CHAR(9) PRIMARY KEY,

​ Sname CHAR(8) NOT NULL

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

);

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

​ 插入元组或修改属性的值时，若不满足属性上的约束条件，则拒绝执行

元组上的约束条件

​ 在CREATE TABLE 上可以用CHECK短语定义元组上的约束条件，即元组级的限制

例：当学生的性别是男时，起名字不能以"Ms."打头

CREATE TABLE Student（

​ Sno CHAR(9),

​ Sname CHAR(8) NOT NULL,

​ Ssex CHAR(2)

​ PRIMARY KEY(Sno)

​ CHECK(Ssex='女' OR Sname NOT LIKE 'Ms.%')

）

​ CHECK写在表级位置表示对所有的元组进行检查，不满足条件则不允许执行

完整性约束命名字句

​ 对现有的完整性约束进行整合，创建新的完整性约束，并对其进行命名

​ 格式：

​ CONSTRAINT <完整性约束条件名> <完整性约束条件>

​ <完整性约束条件> 包括NOT NULL,UNIQUE,PRIMARY KEY短语,FOREIGN KEY短语,CHECK短语等

例：建立学生登记表Student，要求学号在90000~99999之间，姓名不能去空值，年龄小于三十，性别只能是"男"或"女"

CREATE TABLE Student(

​ Sno INT(9) CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),

​ Sname CHAR(20) CONSTRAINT C2 NOT NULL,

​ Sage INT(20) CONSTRAINT C3 CHECK(Sage<30)

​ Ssex CHAR(2) CPMSTRAINT C4 CHECK(Ssex IN('男','女')),

​ CONSTRAINT StudentKey PRIMARY KEY(Sno)

);

修改表中的完整性限制

使用ALTER TABLE语句修改表中的完整性限制

例1：去掉上例对性别的限制

​ ALTER TABLE Student DROP CONSTRAINT C4;

例2：修改上例的约束条件，年龄由<30改为<40

​ 可以先删除原来的约束条件，在增加新的约束条件

​ ALTER TABLE Student DROP CONSTRAINT C3;

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

域中的完整性限制

​ 域即属性的取值范围，SQL可以用CREATE DOMAIN 语句建立一个域以及域应该满足的完整性约束条件，然后用域定义属性

例1：建立一个性别域，并声明性别域的取值范围

​ CREATE DOMAIN GenderDomain CHAR(2)

​ CHECK(VALUE IN('男','女'));

​ CREATE DOMAIN 创建域GenderDomain，取值属性为CHAR(2)，取值范围为('男','女')

​ CHECK语句的效果和上面一样，进行限制

​

还可以用CONSTRAINT 语句对域的限制进行命名

例2：建立一个性别域，并声明性别域的取值范围

​ CREATE DOMAIN GenderDomain CHAR(2)

​ CONSTRAINT GD CHECK(VALUE IN('男','女'));

3.删除域GenderDomain中的限制

ALTER DOMAIN GenderDomain DROP CONSTRAINT GD ;

4.修改域GenderDomain的限制，在其上增加一个限制条件GDD

ALTER DOMAIN GenderDomain

ADD CONSTRAINT GDD CHECK(VALUE('1','0'))

断言

断言可以使用聚集函数进行限制

创建断言

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

​ CHECK子句中的约束条件WHERE子句条件表达式类似

例：限制课程C1最多60名学生选修

​ CREATE ASSERTION ASS_C1

​ CHECK(60>=(

​ SELECT COUNT(*) FROM Course,SC

​ WHERE SC.Cno=Course.Cno AND Course.Cname='C1'

​ )

);

​ 查询选修了C1课程的学生，查询到的结果数量要<=60

删除断言

​ DROP ASSERTION <断言名>

​ 断言使用聚集函数，通常比较复杂，系统维护的成本就会比较高

触发器

​ 触发器是用户定义在关系表上的一类事件驱动的过程

​ (表上发生了某种事件会触发触发器，触发器执行相应的工作)

• 触发器保存在数据库系统中

• 任何用户对表的增删改操作均由服务器自动激活触发器

定义触发器

​ 触发器又叫事件-条件-动作规则

​ 语句格式为：

​ CREATE TRIGGER <触发器名>

​ {BEFORE|AFTER} <触发事件> ON 表名

​ REFERENCING NEW|OLD ROW AS <变量>

​ FOR EACH{ROW |STATEMENT}

​ [WHEN <触发条件>] <触发动作点>

BEFORE|AFTER：在事件发生之前/之后执行

REFERENCING NEW|OLD

FOR EACH{ROW |STATEMENT}：

[WHEN <触发条件>] <触发动作点>

​ 当特定的事件发生时，触发器会先检查事件是否满足触发条件，满足后执行规则中的动作

​ 如果没有定义WHEN <触发条件>，则会立即执行

• ​ 表的拥有者(建表人)才可以在表上创建触发器
• ​ 触发器名可以包含模式名，也可以不包含模式名；同一模式下，触发器名必须是唯一的。触发器名和表名必须在同一模式下
• ​ 触发器只能定义在基本表上，不能定义在视图上。当基本表的数据发生变化时，将激活定义在该表上相应条件的触发器

触发器分为两类：行级触发器FOR EACH ROW和语句级触发器FOR EACH STATEMENT

例：假设Student表的一个触发器的触发条件是：

​ UPDATE Student Set Sno='5'

​ 假设Student表有1000行

​ 若该触发器是行级触发器，则在每一行都被触发，会执行1000次

​ 若该触发器是语句级触发器，则只会在执行这个触发条件的语句时执行，即只执行一次

​ 执行的动作可以是一个PL/SQL过程块，也可以是对已创建存储过程的调用

​ 如果触发器动作执行失败，则会导致激活触发器的事件停止执行

​ 对于行级触发器，用户可以在动作中使用NEW和OLD引用事件之后产生的新值以及该值的旧值

​ 对于语句级触发器，不能使用NEW和OLD