数据库原理：数据库完整性

accttodo 12/31/2023 数据库

参考：

链接1：【最全】《数据库原理及应用》知识点整理+习题 (opens new window)
链接2：数据库原理 (opens new window)
链接3：数据库原理知识梳理 (opens new window)

# 数据库原理：数据库完整性

# 一、数据库完整性的概念

数据库的完整性是指数据的正确性和相容性。数据库的完整性是为了防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据。

为了维护数据库的完整性，DBMS必须能够：

提供定义完整性约束条件的机制：完整性一般由 SQL 的 DDL 语句来实现，它们作为数据库模式的一部分存入数据字典中。
提供完整性检查的方法：检查数据是否满足完整性约束条件的机制。
违约处理：若用户违背了完整性约束条件，应该采取的动作。

安全性和完整性的联系和区别

数据库的安全是指保护数据库以防止不合法的使用所造成的数据泄露、更改或破坏。数据库的完整性是指数据的正确性和相容性。数据库的完整性视为防止数据库中存在不符合语义的数据，防止错误的信息输入和输出。数据库的安全性是保护数据库防止恶意的破坏和非法的存储。也就是说，完整性措施的防范对象是不合法的语义数据，安全性措施的防范对象是非法用户和非法操作。

# 二、数据库完整性的分类

# 实体完整性

关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义。一种是定义为列级约束条件，另一种是定义为表级约束条件。

违约处理：

当用户程序对基本表插入一条记录或对主码列进行更新操作时，会进行如下检查

（1）检查主码值是否唯一，如果不唯一则拒绝插入或修改。

（2）检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。

示例

# 定义为列级约束条件
## 在列级定义主码
CREATE TABLE Course (
	Cno CHAR ( 9 ) PRIMARY KEY,
	Cname CHAR ( 20 ) NOT NULL,
);

# 定义为表级约束条件
## 在表级定义主码
CREATE TABLE Student (
	Sno CHAR ( 9 ),
	Sname CHAR ( 20 ) NOT NULL,
	Ssex CHAR ( 2 ),
	Sage SMALLINT,
	Sdept CHAR ( 20 ),
	PRIMARY KEY ( Sno ) 
);
## 在表级定义主码，属性组定义为主码，只能在表级定义
CREATE TABLE Student (
	Sno CHAR ( 9 ),
	Sname CHAR ( 20 ) NOT NULL,
	Ssex CHAR ( 2 ),
	Sage SMALLINT,
	Sdept CHAR ( 20 ),
	PRIMARY KEY ( Sno, Sname ) 
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 参照完整性

关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。

违约处理：

对被参照表和参照表进行增删改操作时都会进行检查，当不一致发生时，系统可以采用如下策略处理：

（1）拒绝(NO ACTION)执行：不允许该操作执行，该策略一般设置为默认策略。

（2）级联(CASCADE)操作：当删除或修改被参照表的一个元组导致与参照表的不一致时，删除或修改参照表中的所有导致不一致的元组。

（3）设置为空值：当删除或修改被参照表的一个元组时造成了不一致，则将参照表中所有造成不一致的元组的对应属性设置为。

如果要采取非默认策略需要显式地加以说明，如：ON DELETE NO ACTION、ON UPDATE CASCADE。

示例

例如，关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno,Cno）是主码。Sno、Cno分别参照引用Student表的主码和Course表的主码

# 在表级定义参照完整性
CREATE TABLE SC (
	Sno CHAR ( 9 ) NOT NULL,
	Cno CHAR ( 4 ) NOT NULL,
	Grade SMALLINT,
	PRIMARY KEY ( Sno, Sname ),
	FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ),
	FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) 
);

1
2
3
4
5
6
7
8
9

# 用户自定义完整性

1.属性上的约束条件的定义：NULL/NOT NULL、DEFAULT，UNIQUE、CHECK

2.元组上的约束条件：使用CHECK短语定义元组上的约束条件

违约处理：拒绝执行

示例

CREATE TABLE SC (
	Sno CHAR ( 9 ) NOT NULL,
	Cno CHAR ( 4 ) NOT NULL,
	Grade SMALLINT,
	PRIMARY KEY ( Sno, Cno ),
	CHECK ( Sno = 'x' OR Cno NOT LIKE 'MS.%' ) 
);

1
2
3
4
5
6
7

# 三、触发器（Trigger）

触发器是用户定义在关系表上的一类由事件驱动的特殊过程，其不仅可以用于数据库完整性检查，也可以用来实现数据库的其他功能，包括数据库安全性，以及一些业务流程和控制流程。

定义触发器：

CREATE TRIGGER <触发器名>    /*每当触发事件发生时，该触发器被激活*/
{BEFORE | AFTER} <触发事件> ON <表名> /*指明触发器激活的时间是在执行触发事件前或后*/
REFERENCING NEW|OLD ROW AS <变量> /*REFERENCING 指出引用的变量*/
FOR EACH {ROW | STATEMENT}             /*定义触发器的类型，指明动作体执行的频率*/
[WHEN <触发条件> ]                         /*仅当触发条件为真时才执行触发动作体*/
<触发动作体>

1
2
3
4
5
6

多个执行语句的触发器：

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END

1
2
3
4
5

删除触发器：DROP TRIGGER <触发器名> ON <表名>

要点：

只有创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器
触发器名唯一
触发器只能定义在基本表上，不能定义在视图上
触发事件可以是INSERT DELETE 或 UPDATE，也可以是这几个事件的组合，如INSERT OR DELETE等，还可以是UPDATE OF <触发列, …>指明修改哪些列时激活触发器。
AFTER / BEFORE 是触发的时机，AFTER表示在触发事件的操作执行之后激活触发器。
触发器类型：FOR EACH ROW（行级触发器）和FOR EACH STATEMENT（语句触发器），假设表TEACHAR 有1000行，行级触发器将执行1000次，而语句触发器只执行1次
触发条件：触发器被激活时，只有当触发条件为真时触发动作体才执行；否则触发动作体不执行。如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行。
触发动作体：在过程体中可以使用NEW和OLD来引用UPDATE/INSERT事件之后的新值和之前的旧值，如果是语句级触发器则不能使用NEW或OLD引用。

示例

示例

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;

1
2
3
4
5
6
7
8
9
10
11

数据库原理：4、数据库安全性数据库原理：6、范式

知识库

Choose mode