乐趣区

关于mysql:数据库系统概论王珊第五章数据库完整性第一二三节数据库三大完整性

jiezi
  • pdf 下载：明码 7281
  • 专栏目录首页：【专栏必读】（考研复试）数据库系统概论第五版（王珊）专栏学习笔记目录导航及课后习题答案详解

在（数据库系统概论 | 王珊）第二章关系数据库 - 第二节、第三节：关系操作和关系完整性这一节中对数据库的三大完整性已有介绍，而本章的内容就是 如何在 SQL 语言中实现这些完整性规定

一：数据库完整性概述

（1）数据库完整性的基本概念

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

  • 正确性：数据是合乎事实世界语义、反映以后理论情况的。例如性别只能是男或女
  • 相容性：是指数据库同一对象在不同关系表中的数据是合乎逻辑的。比如说年龄个别都在 1 -100 岁，当然也有超过一百岁的，反正没有两百岁，三百岁成仙的人类

（2）数据库完整性和安全性的区别

完整性 ：是为了避免数据库中存在 不合乎语义的数据 ，也就是避免数据库中存在 不正确的数据 。因而，完整性检查和管制的防备对象是 不合语义的、不正确的数据，避免它们进入数据库

安全性 ：是爱护数据库 避免歹意毁坏和非法存取 。因而，安全性管制的防备对象是 非法用户和非法操作，避免他们对数据库数据的非法存取

（3）为保护完整性 DBMS 必须要实现的性能

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

  • 完整性约束条件：是数据库中数据必须满足的语义条件规定
  • SQL 规范应用了一系列概念来形容完整性，包含关系模型的 实体完整性、参照完整性和用户定义完整性

2. 提供完整性检查的办法

  • 完整性检查：检查数据是否满足完整性规定的机制

3. 进行守约解决

二：实体完整性

实体完整性：若属性 A 是根本关系 $R$ 的主属性，则属性 A 不能取空值

（1）定义实体完整性

定义方法：关系模型的实体完整性在 CREATE TABLE 中用 PRIMARY KEY 定义。留神

  • 如果主码仅有一个属性（单属性）：能够定义为 列级约束条件 也能够定义为 表级约束条件
  • 如果主码有多个属性 ：留神 仅能定义为表级约束条件

演示：

如下有几个典型的例子

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

(1)在列级定义主码
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY，Sname CHAR(20) NOT NULL，Ssex CHAR(2)，Sage SMALLINT，Sdept CHAR(20)
);

(2)在表级定义主码
CREATE TABLE Student
(Sno CHAR(9)，Sname CHAR(20) NOT NULL，Ssex CHAR(2)，Sage SMALLINT，Sdept CHAR(20)，PRIMARY KEY (Sno)
); 

(3)将 SC 表中的 Sno，Cno 属性组定义为码
CREATE TABLE SC
(Sno CHAR(9) NOT NULL，Cno CHAR(4) NOT NULL，Grade SMALLINT，PRIMARY KEY (Sno，Cno)     /* 只能在表级定义主码 */
);

（2）实体完整性检查和守约解决

每当插入或对主码列进行更新操作时，DBMS 依照实体完整性规定主动进行查看，包含

  • 查看主码值是否惟一，如果不惟一则回绝插入或批改
  • 查看主码的各个属性是否为空，只有有一个为空就回绝插入或批改

其中查看记录中主码值是否惟一有两种办法

  • 全表扫描：非常耗时
  • 建设索引：关系数据库管理系统个别都会在主码上主动建设一个索引

三：参照完整性

（1）定义参照完整性

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

演示：

定义 sc 表的时候，其 (Sno,Cno) 是主码，别离参照 Sudent 的主码和 Course 表的主码

CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
    Sno CHAR(4) NOT NULL,
    Grade SMALLINT,
    
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno),
    FOREIGN KEY(Cno) REFERENCES Course(Cno)
);

（2）参照完整性检查和守约解决

A：毁坏完整性的行为

参照完整性将表与表分割在了一起，所以对其中一个表的批改很可能会影响到另外一张表

举个例子，被参照表是Student，参照表是sc，毁坏参照完整性的行为及其守约解决如下表所示

对于参照表 sc 的行为

  • 向 sc 表（参照表）中插入一个元组，这是会被回绝的 。因为有可能你所插入的元组的Sno（外码）无奈在Student 表中找到，这就意味着在成绩表中插入了一个非本班同学的问题，这显然是不合理的
  • 批改 sc 表（参照表）中的一个元组，这是会被回绝的 。因为有可能你会批改该元组的Sno（外码），这就可能导致Sno 无奈在 Student 表中好到
  • 删除 sc 表（参照表）中的一个元组，这是可行的。因为它无非就是一条问题信息

对于被参照 Student 的行为

  • 删除 Student 表（被参照表）中的一个元组，这是会被回绝（也有可能级联删除或设为 NULL）的。因为删除一个元组后，该元组所对应的Sno（主码）将不复存在，这就有可能导致sc 表（参照表）中某些元组的 Sno（外码）在Student 表中找不到
  • 批改 Student 表（被参照表）中的一个元组，这是会被回绝（也有可能级联删除或设为 NULL）的。因为一旦批改了该元组的Sno 属性，就会产生和下面一样的问题
  • 向 Student 表（被参照表）插入一个元组，这是可行的。因为它无非就是一个新同学嘛

B：守约解决措施

①：回绝

回绝 ：不容许该操作执行，为 默认策略

②：级联

级联 ：当删除或批改Student 表（被参照表）的一个元组导致与 sc 表（参照表）不统一时，则会删除或批改 sc 表（参照表）中的所有导致不统一的元组

  • 比方删除 Student 表中 Sno 为“201215121”的元组后，则会从 sc 表中级联删除 sc.Sno='201215121' 的所有元组
  • 留神： 在这个例子中，依照语义环境抉择级联删除是正确的，因为学生信息一旦删除了，那么问题信息就没有存在的意义了
③：设为空值

假如有上面两个关系（划线为主码）

  • 参照表为 学生 表
  • 被参照表为 业余 表

设为空值 ：当删除或批改 业余 表（被参照表）的一个元组时造成了不统一，则 将学生 表（参照表）中的所有造成不统一的元组的对应属性（业余号）设为空值

  • 比方删除 业余 表中业余号为 12 的业余，那么接着就要把 学生 表中业余号 =12 的所有元组的业余号设置为空值
  • 留神：在这个例子中，依照语义环境抉择设置为空值是正确的，因为业余一旦删除，表明该学生须要重新分配业余，除非该学生毕业，否则不可能把学生信息给删除

其中对于外码 是否能够取空值，在后面的文章曾经有具体介绍：（数据库系统概论 | 王珊）第二章关系数据库 - 第二节、第三节：关系操作和关系完整性

C：SQL 实现

综上，在定义完整性束缚时还要给出相应的解决策略

CREATE TABLE SC
(Sno CHAR(9)
    Sno CHAR(4) 
    Grade SMALLINT,// 是外码也是主码，故不能取空值，实体完整性
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno)
        ON DELETE CASCADE // 删除 Student 表的元组时，级联删除本表相应元组
        ON UPDATE CASCADE, // 更新 Student 表中的 Sno 时，级联更新本表相应元组
    FOREIGN KEY(Cno) REFERENCES Course(Cno)
        ON DELETE NO ACTION // 删除 Course 表的元组造成不统一时，拒绝执行
        ON UPDATE CASCADE // 更新 Course 表中的 Cno 时，级联更新本表相应元组
);
  • 策略能够依据具体环境、语义抉择

四：用户自定义完整性

用户自定义完整性针对某一具体关系数据库的约束条件，反映 某一具体利用所波及的数据必须满足的语义要求

（1）属性上的约束条件

A：不容许取空值（NOT NULL）

<font color=”0000fff”> 演示

CREATE TABLE SC
(Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT NOT NULL,
    PRIMARY KEY(Sno,Cno)// 定义了实体完整性，隐含了不容许取空值，在列级中可不写
);

B：列值惟一（UNIQUE）

<font color=”0000fff”> 演示

CREATE TABLE DEPT
(Deptno NUMERIC(2),
    Dname CHAR(9) UNIQUE NOT NULL, // 惟一且不能取空值
    Location CHAR(10),
    PRIMARY KEY(Depto)
);

C：满足指定条件（CHECK）

<font color=”0000fff”> 演示

例如 Ssex 只能取“男”或“女”

CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2) CHECK(Ssex IN ('男','女')),
    Sage SMALLINT,
    Sdept CHAR(20)
);

例如 Grade 的取值范畴为[0,100]

CREATE TABLE SC
(Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT CHECK(Grade >= 0 AND Grade <= 100),
    
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno),
    FOREIGN KEY(Cno) REFERENCES Course(Cno)
);

（2）元组上的约束条件

同属性值限度相比，元组级的限度能够设置 不同属性之间 的取值的互相约束条件

<font color=”0000fff”> 演示

例如，规定插入男性时，其名字不能以 Ms. 结尾

CREATE TABLE Student
(Sno Char(9),
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20),
    PRIMARY KEY(Sno),

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

);
  • 是女性时条件成立能够插入
  • 是男性时，同时还要判断其姓名不能以 Ms. 结尾
退出移动版