【吐血整理】数据库的完整性

本文主讲 数据库完整性，欢迎阅读~

---

前言

数据库的完整性：

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

【例如】：学生的学号必须唯一、性别只能是男或女、本科学生年龄的取值范围为14~50的整数、学生所选的课程必须是学校开设的课程，学生所在的院系必须是学校已成立的院系
— — — — — — — — — — — — — — — — — —
数据的“完整性”和“安全性”是两个不同概念：
数据的完整性
① 防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据
② 防范对象：不合语义的、不正确的数据
数据的安全性
① 保护数据库 防止恶意的破坏和非法的存取
② 防范对象：非法用户和非法操作

---

一、实体完整性

1. 实体完整性定义

关系模型的实体完整性： CREATE TABLE中用PRIMARY KEY定义
🌟来看例子： 将Student表中的Sno属性定义为码：
ps：因为我之前的STUDENT数据库中已经有Student表了，所以我新建了SCHOOL数据库用户本文的实验

CREATE DATABASE SCHOOL;

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)
); 

这里主码只包含一个属性，所以列级定义和表级定义均可（不过就一个的话直接在列级定义叭，毕竟写的单词少一点哈哈）。
如果主码由多个属性组成，即联合主码（2个及其以上的属性构成主码），可参照下一个例子，那就只能在表级定义了，要在列级定义的话就会出现多条PRIMARY KEY，这是不允许的（因为一张表中只能有一个主码！）

🌟Another one： 将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）是主码。Sno，Cno分别参照Student表的主码和Course表的主码，看下面的例子：
🌟来看例子： 定义SC中的参照完整性：

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),   /*在表级定义参照完整性*/
	FOREIGN KEY (Cno) REFERENCES Course(Cno)    /*在表级定义参照完整性*/
);

这里需要重新创建SC表，且要用到Course表，所以我先来删除刚刚创建的SC表并创建一张Course表：

DROP TABLE SC;    /*删除上面的实体完整性时的例子创建的SC表*/
CREATE TABLE Course (	
	Cno CHAR(4) PRIMARY KEY,
	Cname CHAR(40),            
	Cpno CHAR(4),               	                      
	Ccredit SMALLINT,
	FOREIGN KEY (Cpno) REFERENCES Course(Cno) /* 表级完整性约束条件，Cpno是外码，被参照表是自身*/
 );

好了，现在可以创建该例子中的SC表了

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

可能破坏参照完整性的情况及违约处理如下图：

比如：有下面2个关系
学生（学号，姓名，性别，专业号，年龄）
专业（专业号，专业名）
假设专业表中某个元组被删除，专业号为 12
按照设置为空值的策略，就要把学生表中专业号为12的所有元组的专业号设置为空值
对应语义：某个专业删除了，该专业的所有学生专业未定，等待重新分配专业
（如果不提前将其设置为空值的话因为有参照完整性的约束，系统会拒绝执行该删除操作）

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

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

可以发现该SQL语句和之前的相比多出了：

ON DELETE CASCADE        /*级联删除SC表中相应的元组*/
ON UPDATE CASCADE,      /*级联更新SC表中相应的元组*/
ON DELETE NO ACTION 	/*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
ON UPDATE CASCADE    /*当更新course表中的cno时，级联更新SC表中相应的元组*/

加上这样的描述之后可读性会更高一些，更容易去理解参照完整性~

---

三、用户定义的完整性

用户定义的完整性是：针对某一具体应用的数据必须满足的语义要求

1. 属性上的约束条件

CREATE TABLE时定义属性上的约束条件
① 列值非空（NOT NULL）
② 列值唯一（UNIQUE）
③ 检查列值是否满足一个条件表达式（CHECK）
— — — — — — — — — — — — — — — — — — — — — —
1）不允许取空值
🌟来看例子： 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值：

CREATE TABLE SC (  
	Sno CHAR(9)  NOT NULL,	
	Cno CHAR(4)  NOT NULL,	
	Grade  SMALLINT