「MySQL学习笔记04」数据库完整性与设计

¶4.1 数据库的完整性

¶4.1.1 数据库完整性的概述

数据库的完整性（integrity）是指数据的正确性（correctness）和相容性（compat-ability）。

数据的正确性：指数据是符合现实世界语义、反映当前实际状况的；
数据的相容性：指数据库同一对象在不同关系表中的数据是符合逻辑的；

数据的完整性，是为了防止数据库中存在不符合语义的数据，即存在不正确的数据。

数据的安全性，是保护数据库防止恶意破坏和非法存取。防范对象是非法用户和非法操作。

数据库完整性的定义一般由SQL的数据定义语言（DDL）来实现，它们作为数据库模式的一部分存入数据字典中，在数据库数据修改时，关系数据库管理系统的完整性检查机制，将按照数据字典中定义的这些约束进行检查。

约束种类	约束关键字	保证的完整性	描述
主键约束	`PRIMARY KEY`	实体完整性	如果主属性取空，说明存在某个不可标识（不可区分）的实体，违背实体完整性规则
唯一约束	`UNIQUE`	用户定义的完整性（属性上的约束条件）	关系模型应提供定义和检验这类完整性的机制，以便用统一的系统的方法处理而不需由应用程序去承担这一功能。
非空约束	`NOT NULL`	用户定义的完整性（属性上的约束条件）
外键约束	`FOREIGN KEY`	参照完整性	比如，学生关系引用了专业关系的主码“专业代码”，也就说学生关系中的某个属性取值需参照专业关系的属性取值。
检查约束	`CHECK`（注：`MySQL`不支持😅）	用户定义的完整性（元组上的约束条件）

¶4.1.2 主键约束

一、创建主键

主键关键字：PRIMARY KEY
主键的特点：
1. 非空且唯一。
2. 主键就是表中记录的唯一标识，一张表只能有一个字段作为主键。
  要保证记录中主键（主码）值是否唯一的唯一方法是全盘扫描。为了避免对表进行全盘扫描导致的过多耗时，关系数据库管理系统一般都在主码上自动建立一个索引。
通常为每张表设计一个id的字段作为主键，而不是业务字段（如身份证，学号等）。主键不需要给最终客户使用，它是否有含义都问题不大。

创建主键方式：

在创建表的时候为字段添加主键
1
字段名字段类型 PRIMARY KEY

在已有的表中添加主键

1	`ALTER TABLE 表名 MODIFY 字段名字段类型 PRIMARY KEY;`

二、删除已有的主键

1	`ALTER TABLE 表名 DROP PRIMARY KEY;`

¶主键自增

通过AUTO_INCREMENT，我们能够在每次插入新记录时，数据库自动生成主键字段的值。（字段类型必须是整数类型）起始值默认为1。

注意：删除所有的记录，对自增的影响：
DELETE ：删除所有记录之后，起始值是之前的值+1；
TRUNCATE：起始值为默认值，重新开始。

创建表的同时添加主键约束，并完成主键自增。举例使用如下：

CREATE TABLE stu(
	  id INT PRIMARY KET AUTO_INCREMENT,
    name VARCHAR(20)
);

CREATE TABLE STB(
	  id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
) AUTO_INCREMENT=起始值;

已有的表中设置主键自增

1
2
3

ALTER TABLE 表名 MODIFY 字段 字段类型 AUTO_INCREMENT;

ALTER TABLE 表名 AUTO_INCREMENT = 起始值;

删除自动增长

1	`ALTER TABLE 表名 MODIFY 字段字段类型 ;`

¶4.1.3 非空约束

NOT NULL，即值不允许为 NULL。

创建表的同时添加非空约束。举例使用如下：

CREATE TABLE stu(
	  id INT,
    name VARCHAR(20) NOT NULL /* name 不允许为空 */
);

已有的表中添加非空约束。

1	`ALTER TABLE 表名 MODIFY 字段字段类型 NOT NULL;`

删除某一字段的非空约束

1	`ALTER TABLE 表名 MODIFY 字段字段类型; /* 注意，是MODIFY，不是DROP*/`

另外，指定默认值——DEFAULT

创建表的同时设置默认值。举例使用如下：

CREATE TABLE stu(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  address VARCHAR(20) DEFAULT "Guangzhou" /* 这里建议使用英文（中文似乎出锅） */
);

在已有表中添加记录，其中使用默认值，表如上，举例如下：

1
2
3

INSERT INTO st2 VALUES(1, 'Luffy', DEFAULT);

INSERT INTO st2 VALUES(2, 'Zoro', '深圳'); /* 覆盖掉默认值 */

¶4.1.4 唯一约束

唯一约束也可称为唯一索引。UNIQUE，即值不允许在表中有重复。

注意，在MySQL中，唯一约束限定的列的值，允许存在多个NULL。

创建表的同时添加唯一约束。举例使用如下：

CREATE TABLE stu(
	  id INT,
    phone_number VARCHAR(20) UNIQUE
);

已有的表中添加唯一约束。

1	`ALTER TABLE 表名 MODIFY 字段字段类型 UNIQUE;`

删除某一字段的唯一约束

1	`ALTER TABLE 表名 DROP INDEX 字段; /* 注意，与其他语句稍有不同！*/`

¶4.1.5 外键约束

FOREIGN KEY，让一个表与另一个表产生关系。

问题（1）：单表有时候会有如下类似的缺点，①数据冗余（比如，"dep_name"与"dep_location"是一一对应的）；②后期增删改较麻烦。

解决方案：建立两个表，同时利用外键 dep_id，将两表联系起来。

问题（2）：然而，当我们在“员工表”中插入不存在的部门（即“部门表”中不存在该部门），系统却允许添加，显然不符合实际情况。

解决方案：对外键dep_id进行约束，使其只能是“部门表”中已存在的值。

至此，创建与删除外键约束的语法如下：

创建表的同时添加外键：

CREATE TABLE 表名(
	...
    外键字段名称 字段类型
    CONSTRAINT 外键名称 FOREIGN KEY (外键字段的名称) REFERENCES 主表名称(主表列的名称);
    /* 注意，外键名称与外键列名称不一定相同，"CONSTRAINT 外键名称"可以不定义 */
);

在已有表中设置外键约束：

1	`ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);`

删除外键

1	`ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; /* 注意，是DROP */`

¶级联

例如，对于学生表、学生成绩表和中间表（其一个元组表示一个学生某门课程的成绩），

有4种可能破坏参照完整性的情况：

一般而言，当上述不一致发生时，系统默认采用“拒绝”（NO ACTION）操作。如果想让系统采用其他策略，则需要在创建参照表时，显示地加以说明（如，级联（CASCADE）操作）

ON UPDATE CASCADE：级联更新
ON DELETE CASCADE：级联删除
NO ACTION：不允许该操作的执行

举例使用：（针对上述问题）

CREATE TABLE SC ( 
    SNo CHAR(9), /* 学生成绩表的主属性*/
    CNo CHAR(4), /* 学生表的主属性的外键 */
    Grade SMALLINT,
    PRIMARY KEY(SNo, CNo), /* 在表级定义实体完整性（设置联合主键）*/
    FOREIGN KEY (SNo) REFERENCES Student(SNo) /* 注意区别两个SNo*/
            ON DELETE CASCADE /*当删除Student表中的元组时，级联删除SC中相应元组 */
            ON UPDATE CASCADE, /*当更新Student表中的SNo时，...*/
    FOREIGN KEY (CNo) REFERENCES Course(CNo) 
            ON DELETE CASCADE /*当删除Course表中的元组造成与SC表不一致时，拒绝删除*/
            ON UPDATE CASCADE /*当更新Course表中的CNo时，级联更新SC表中相应的元组*/
);

¶4.2 数据库的设计

¶4.2.1 多表之间的关系

分类	举例	实现方式
一对一	一个人只有一个身份证，一个身份证只能对应一个人	可在任意一方添加唯一外键指向另一方的主键
一对多	一个部门有多个员工，一个员工只能对应一个部门	在多的一方建立外键，指向少的一方的主键
多对多	一个学生可选多门课程，一门课程可被多个学生选择	需借助第三张表，中间表至少包含两个字段（共同作为该表的外键），分别指向两张表的主键

¶4.2.2 函数依赖与码

¶函数依赖

函数依赖：若通过 $A$ 属性（属性组）的值，可唯一确定 $B$ 属性的值，则称 $B$ 依赖于 $A$ 。记作： $A\rightarrow B$
完全函数依赖：若 $A$ 是一个属性组，则 $B$ 属性值的确定需要依赖于 $A$ 属性组中所有的属性值，则称 $B$ 完全依赖于 $A$ ，记作： $X\stackrel{F}{\longrightarrow}Y$
部分函数依赖：若 $A$ 是一个属性组，则 $B$ 属性值的确定只需要依赖于 $A$ 属性组中某一些值即可，则称 $B$ 属性部分依赖于 $A$ ，记作： $X\stackrel{P}{\longrightarrow}Y$
传递函数依赖：若通过 $A$ 属性（属性组）的值，可唯一确定 $B$ 属性（属性组）的值，再通过 $B$ 属性（属性组）的值可唯一确定 $C$ 属性的值，则称 $C$ 传递函数依赖于 $A$ ，记作： $X\stackrel{传递}{\longrightarrow}Y$

¶码

设 $R(U)$ 是属性集 $U$ 上的关系模式，而 $K$ 为 $R<U, F>$ 中的属性或属性组合

候选码：若 $U$ 完全依赖于 $K$ ，则 $K$ 为 $R$ 的候选码。
主码（primary key）：若候选码多于一个，则选定其中的一个为主码。
主属性（primary attribute）：包含在任何一个候选码中的属性称为主属性。相反地，若不包含在任何候选码中的属性称为非主属性或 非码属性（non-key attribute）。

¶4.2.3 范式与规范化的概念

关系数据库中的关系是要满足一定要求的，满足不同程度要求的为不同范式。

⭐一个低一级范式的关系模式通过 模式分解 可以转换为若干个高一级范式的关系模式的集合，这种过程就叫 规范化。

目前关系数据库有六种范式，而满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

¶4.2.4 第一范式

数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。

不符合1NF的例子	符合1NF的例子

¶4.2.5 第二范式

在满足第一范式的前提下，表中的每一个字段都完全依赖于主键（不能存在仅依赖主键一部分的列）。可以理解为“一张表只描述一件事情”。

简而言之，第二范式就是在第一范式的基础上所有列完全函数依赖于主键列。

当存在一个复合主键包含多个主键列的时候，有可能发生不符合第二范式的情况。

不符合2NF的例子	符合2NF的例子

左表中，学号与课程名称，确定分数列；但学号，能够确定姓名、系名、系主任（即，后三者部分依赖于“学号-课程名称”）

¶4.2.6 第三范式

在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其他列来间接依赖主键。

简而言之，在满足2NF的基础上，任何非主列不得传递函数依赖于主键。

不符合3NF的例子	符合3NF的例子

右表中存在一定的传递函数依赖：学号 $\rightarrow$ 系名 $\rightarrow$ 系主任
数据添加的问题：仅从这两个表，无法实现新增一个系与系主任。
数据删除的问题：如果删除第一列，相应地“经济系-张三丰”也不存在于两个表中了。