数据库完整性

title: 数据库完整性
date: 2020-12-12 11:05:07
tags: 数据库

本章目标：

  • [ ] 什么是数据库完整性
    • [ ] 实体完整性
    • [ ] 参照完整性
    • [ ] 用户自定义完整性
  • [ ] 用SQL语言定义关系模式的完整性约束条件
  • [ ] 重点：
    • [ ] DBMS完整性控制的实现机制：定义，检查，违约
    • [ ] SQL语言定义关系模式的完整性条件
  • [ ] 难点：
    • [ ] RDBMS如何进行违约处理，其中比较复杂的是参照完整性的实现机制

数据库完整性

数据库的完整性 = 数据的正确性 + 数据的相容性

  • 数据的正确性：符合现实世界的语义，反映当前的实际状况（学号唯一，性别男女，成绩范围
  • 数据的相容性：数据库的同一对象在不同关系表中的数据是否符合逻辑

对比数据库安全性

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

完整性是阻止合法用户通过合法操作向数据库中加入不正确的数据；

安全性防范是非法用户和非法操作存取数据库中的正确数据。

为维护数据库的完整性，数据库管理系统必须

  • [x] 提供定义完整性约束条件的机制

    • 完整性约束条件也称为完整性规则，是数据库中的数据必须满足的语义约束条件
    • SQL标准使用了一系列概念来描述完整性，包括关系模型的实体完整性、参照完整性和用户定义完整性
    • 这些完整性一般由SQL的数据DDL定义语言语句来实现

  • [x] 提供完整性检查机制

    • 数据库管理系统中检查数据是否满足完整性约束条件的机制称为完整性检查。
    • 一般在INSERT、UPDATE、DELETE语句执行后开始检查，也可以在事务提交时检查

  • [x] 违约处理： 数据库管理系统若发现用户的操作违背了完整性约束条件就采取一定的动作

    • 拒绝（NOACTION)执行该操作>
    • 级连（CASCADE)执行其他操作

5.1 实体完整性

关系模型的实体完整性

  • CREATE TABLE中用PRIMARY KEY
  • 定义单属性构成的码有两种说明方法
    • 定义为列级约束条件
    • 定义为表级约束条件
  • 对多个属性构成的码只有一种说明方法定义为表级约束条件
# 列级定义主码
CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
 	Sname CHAR(20)NOT NULL,
 	Ssex CHAR(2),
 	Sage SMALLINT,
 	Sdept CHAR(20));
# 表级定义主码
CREATE TABLE Student ( 
    Sno CHAR(9),
    Sname CHAR(20) NOT NULL,
    ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20),
    PRIMARY KEY (Sno );

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

插入或对主码列进行更新操作时，关系数据库管理系统按照实体完整性规则自动进行检查。

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

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

  • 检查记录中主码值是否唯一的一种方法是进行全表扫描；依次判断表中每一条记录的主码值与将插入记录上的主码值(或者修改的新主码值)是否相同

全表扫描缺点：十分耗时
为避免对基本表进行全表扫描，RDBMS核心般都在主码上自动建立一个索引

image-20201212091411403

5.2 参照完整性

5.2.1参照完整性定义

若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码K相对应（基本关系R和S不一定是不同的关系)
，则对于R中每个元组在F上的值必须为:
空值（F的每个属性值均为空值）
等于S中某个元组的主码值

image-20201212092117174

关系模型的参照完整性定义：

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

image-20201212092401553

image-20201212092417179

image-20201212092454564

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

DBMS什么时候进行参照完整性？会破坏参照完整性的操作 Insert，update

一个参照完整性将两个表中的相应元组联系起来；对被参照表和参照表进行增删改操作时有可能破坏参照完整性，必须进行检查

image-20201212092913220

参照完整性违约处理：
(1）拒绝（NO ACTION）执行
不允许该操作执行。该策略一般设置为默认策略

(2）级联（CASCADE）操作
当删除或修改被参照表( Student）的一个元组造成了与参照表（SC）的不一致，则删除或修改参照表中的所有造成不一致的元组
(3）设置为空值（SET-NULL)
当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。

image-20201212093340391

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

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 /*级联删除*/
	ON UPDATE CASCADE /*级联更新*/
 FOREIGN KEY(Cno) REFERENCES Course(Cno)
	ON DELETE NO ACTION /*当删除Course表中的元祖，造成与SC表不一致时拒绝删除*/
 	ON UPDATE CASECADE
);

5.3 用户定义完整性

概念：针对某一具体应用的数据必须满足的语义要求

关系数据库管理系统提供了定义和检验用户定义完整性的机制，不必由应用程序承担

5.3.1属性上的约束条件

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

  • NOT NULL

  • UNIQUE

  • CHECK 检查列值是否满足一个条件表达式

    候选码：UNIQUE NOT NULL

# 用CHECK短语指定列值应该满足的条件[例5.7] Student表的Ssex只允许取“男”或“女”。
CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2) CHECK (Ssex IN('男','女')), /*性别属性Ssex只允许取"男"或女*/
	Sage SMALLINT,
    Sdept CHAR(20));

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

  • 插入元组或修改属性的值时，关系数据库管理系统检查属性上的约束条件是否被满足

  • 如果不满足则操作被拒绝执行

5.3.2元组上的约束条件

元组上的约束条件定义

属性上约束条件只涉及单个属性，元组级限制则可以设置不同属性之间的取值的相互约束条件

在CREATE TABLE时可以用CHECK子句定义元组上的约束条件

image-20201212095832908

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

  • 插入元组或修改属性的值时，关系数据库管理系统检查元组上的约束条件是否被满足
  • 如果不满足，则拒绝操作

完整性约束：小结

image-20201212100026863

5.4 完整性约束命名子句

1.完整性约束命名子句

  • CONSTRAINT <完整性约束条件名><完整性约束条件>
  • <完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等

image-20201212100423345

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

  • ALTER TABLE 语句，修改表中的完整性限制

删除：ALTER TABLE Student DROP CONSTRAINT C4

修改：先删除，后增加新的约束条件

ALTER TABLE Student DROP CONSTRAINT C1;
ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999)；

5.5 断言

  • SQL中，可以使用CREATE ASSERTION语句，通过声明性断言来指定更具一般性的约束
  • 可以定义涉及计多个表的或聚集操作的比较复杂的完整性约束
  • 断言创建后，任何对断言中所涉及的关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真值得操作都会被拒绝执行

image-20201212101633956

image-20201212101742886

image-20201212101852568

如何现有数据库模式无法满足业务需要，可以更改数据库模式

image-20201212102034660

如果断言很复杂，则系统检测和维护断言的开销比较高，这是在使用断言时应该注意的！

删除断言的语句格式

DROP ASSERTION <断言名>

5.6 触发器

  • 用户定义在关系表上的一类由事件驱动的特殊过程
  • 任何用户对表的增、删、改操作均由服务器自动激活相应的触发器
  • 触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力

定义触发器

只有表的拥有者可以定义触发器

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

触发器名：可以包括模式名也可以不包括模式名；同一模式下，触发器名必须是唯一的；触发器名和模式名必须在同一模式下

触发事件：INSET、DELET、UPDATE；几个事件的组合；UPDATE OF<触发列 ...>

表 名：触发器只能定义在基本表上，不能定义在视图上；当基本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器

行级触发器和语句级触发器

image-20201211205152791

image-20201211205306708

image-20201211205406325

image-20201211205438345

完整性约束是由系统自发触发的，而触发器是由事件触发的

image-20201211205637164

image-20201211210159018

image-20201211210434042

image-20201211210608365

激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行

一个数表上可能定义了多个触发器，遵循如下的执行顺序：

  1. 执行该表上的BEFORE触发器（如果多个，谁先创建谁先执行，也有按照名称排序
  2. 激活触发器的SQL语句
  3. 执行该表上的AFTER触发器

删除触发器

慎重选择触发器，

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

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

5.7 小结

image-20201211211423174

image-20201211211547951

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/aspiration/p/15422775.html