数据库完整性：

指数据库中数据的正确性、相容性

• 正确性：保证进入数据库的数据是符合语义约束的合法数据
• 相容性：同一个事实的两个数据应当是一致的

 

 为了维护数据库的定义完整性：

DBMS必须具备三个功能：（完整性定义、检查控制、违约处理都是由DBMS来完成）

（1）提供定义完整性条件约束的机制（实体完整性，参照完整性，用户定义完整性）

（2）提供完整性检查的方法（一般在 insert 、update 、delete、等语句执行后检查）

（3）违约处理 

完整性约束条件的分类

①就被约束的数据对象而言，完整性约束又可以分为如下表所示的四种类型

约束类型    含义    
类型/域约束    说明给定类型的合法取值    用户自定义的完整性
属性约束         说明属性的合法取值    用户自定义的完整性
关系约束         说明关系的合法取值    实体完整性/用户自定义的完整性
数据库约束    说明数据库的合法取值，通常涉及多个关系    实体完整性/用户自定义的完整性
②从约束的状态的角度，约束还可以分静态约束和动态约束

静态约束是关于数据库正确状态的约束
动态约束是数据库从一种正确状态转移到另一种状态的转移约束

实体完整性（两个关系的不变性之一）：

关系模型的实体完整性在 create table  中用 primary key （主键） 定义。

单属性构成的码有两种说明方法：（1）定义列级约束条件 （2）定义表级约束条件

多属性构成的码只有一种说明方法 ： 定义表级约束条件

建议都使用表级约束条件！（可使条件更加清晰）

①实体完整性定义：

每个关系应该有一个主码（可唯一标识表中的一条记录），每个元组的主码值惟一确定该元组
主码的任何属性都不能取空值

②违反实体完整性规则的操作

插入(insert)新元组时可能破坏实体完整性规则（DBMS自动检查主码是否为空）
修改（updata）元组的主码时可能破坏实体完整性规则（DBMS自动检查主码是否为空）
DBMS应该自动检查是否导致违反实体完整性约束，并拒绝导致破坏实体完整性约束的任何插入或修改
SQL支持实体完整性。用户只需要在创建基本表时说明关系的主码，系统就能够自动地保证实体完整性

 

参照完整性 ( P46 ) ：实现这种引用规则（一对一，多对多），要求外码的取值只是被参照表主码的值或者取空值

关系模型的参照完整性在 create table 中用 foreign key 短语定义那些列为外码

用 references 短语指明这些外码参照哪些表的主码

foreign key (A1,...,An) references <外表名> (<外表主码>)
[<参照触发动作>]
--指出修改和删除违反参照完整性约束时触发的动作；缺省时，违反参照完整性的修改和删除将被拒绝
--参照触发动作可以是以下两种
on update <参照动作> [on delete <参照动作>]
on delete <参照动作> [on update <参照动作>]
--参照动作可以是拒绝、级联、置空值、置缺省值之一
 
--关系SC中（Sno，Cno）是主码。Sno，Cno分别参照Student表的主码和Course表的主码定义SC中的参照完整性
create table SC(
    Sno char(9) not null,
    Cno char(4) not null,
    Grade int,
    primary key (Sno, Cno),  /*在表级定义实体完整性*/
    foreign key (Sno) references Student(SNO),  /*在表级定义参照完整性*/
    foreign key (Cno) references Course(Cno)    /*在表级定义参照完整性*/
    );

①参照完整性规则：

参照关系R的任何元组在其外码FKR上的值或者等于被参照关系S的某个元组在主码Ks上的值，或者为空值

②违反参照完整性的更新

向参照表（外键（foreign key）在的表，如：Sno , Cno）关系R中插入元组
修改参照表关系R外码上的值

删除被参照表（在 references 后面的表，如：Student）关系S的元组
修改被参照表（在 references 后面的表，如：Course）关系S主码上的值

③违约处理（违背完整性约束条件）

向参照关系R插入元组：拒绝（ 默认 ）
修改参照关系R外码上的值：拒绝（ 默认 ）
删除被参照关系S的元组：拒绝/级联（ cascade ）删除/置空值（ set-null ）/置缺省值
修改被参照关系S主码上的值：拒绝/级联删除（ cascade ）/置空值（ set-null ）/置缺省值

④SQL中的参照完整性

外码可以在创建基本表时用FOREIGN KEY子句说明, 形式为：
FOREIGN KEY (A1,…, Ak) REFERENCES <外表名> (<外表主码>) [<参照触发动作>]
<参照触发动作>指：当修改和删除违反参照完整性约束时触发的动作（ 缺省值，违反参照完整性的修改和删除将被拒绝 ）
<参照触发动作>可以是如下两种形式之一：
ON UPDATE <参照动作> [ON DELETE <参照动作>] ON
DELETE <参照动作> [ON UPDATE <参照动作>]

其中<参照动作>可以是CASCADE、SET NULL、SET DEFAULT和NO ACTION 之一，分别表示级联、置空值、置缺省值和拒绝
ON DELETE <参照动作>缺省时，违反参照完整性的删除将被拒绝
ON UPDATE<参照动作>缺省时，违反参照完整性的修改将被拒绝
eg：
如果我们希望在更新Students元组的主码时同时修改相应的SC元组的外码Sno，删除Students的元组时同时删除相应的SC元组；

而更新Courses的元组时同时修改相应的SC元组的外码Cno，但不允许删除Courses的元组破坏参照完整性，则我们可以用如下语句创建基本表SC：

CREATE TABLE SC
(Sno  CHAR (9) not null,
 Cno  CHAR (5) not null,
 Grade SMALLINT CHECK (Grade>=0 AND Grade<=100),
 PRIMARY KEY (Sno,Cno),/*主键*/

 FOREIGN KEY(外键) (Sno) REFERENCES Students（参照学生表） (Sno) 
       ON UPDATE CASCADE /*级联删除SC表中对应的元组*/
       ON DELETE CASCADE, /*级联更新SC表中对应的元组*/
 
FOREIGN KEY (Cno) REFERENCES Courses (Cno) 
       ON UPDATE CASCADE  );       /*当删除course表中的元组造成了SC表不一致时拒绝删除*/

用户定义完整性：

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

DBMS提供了定义和检验这类完整性的机制，使用了和实体完整性、参照完整性相同的方法来处理他们，而不必由应用程序承担。

属性上的约束条件的定义

属性列：

（1）列值非空（not null）

（2）列值唯一 （ unique ）

（3）检查列值是否满足一个条件表达式（ check ）

1、不允许取空值

在定义的列的后面 + not null 

例： 在定义Stdent表时 ，Sname 、Ssex 不允许取空值

2、列值唯一

在定义的列的后面 + unique 

例：建立部门表Dept ，要求部门名称 Dname 列取值唯一，部门编号Deptno列为主码（学院名不能重名）

3、用check 短语指定列值应该满足的条件

例：性别只允许 男 或 女 

sage  char（2）check（ Ssex in （ ‘男’ ，‘ 女 ’ ） ）男和女是常量表达式，需要使用单引号定界

sage smallint check （sage between 18 and 50）（年龄区间就这么写）

元组上的约束条件的定义：（ 表级约束 ）

在create table 时可以用 check 短语定义元组上的约束条件

元组级的限制可以设置不同属性之间的取值的相互约束条件

例：当学生的性别时男时，姓名不能以 Ms. 开头

check（Ssex = ' 女 ' or Sname not null like ' Ms. % '）逻辑或的关系

即：性别时女时，什么开头都可以，当性别是 男时第一个条件不满足，就需要看满足第二个条件，不能以Ms. 开头。

用户自定义的约束条件检查和违约处理

插入元组或修改属性的值时，rdbms检查定义的约束跳进啊是否被满足，如果不满足则操作被拒绝执行。

更多内容可以查看 计算机基础 — 笔记目录

数据库的完整性：

• 数据的正确性
  是指数据是符合现实世界语义，反映了当前实际状况的
  • 学生的学号必须唯一
  • 性别只能是男或女
  • 成绩的取值范围为0~100
• 数据的相容性
  是指数据库同一对象在不同关系表中的数据是符合逻辑的
  • 学生所选的课程必须是学校开设的课程
  • 学生所在的院系必须是学校已成立的院系

数据的完整性和安全性是两个不同概念：

• 数据的完整性
  • 防止数据库中存在不正确的数据
  • 防范对象：不正确的数据
  • 完整性是阻止合法用户通过合法操作向数据库中加入不正确的数据
• 数据的安全性
  • 保护数据库防止恶意的破坏和非法的存取
  • 防范对象：非法用户和非法操作
  • 安全性防范的是非法用户和非法操作存取数据库中的正确数据

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

• 提供定义完整性约束条件的机制
  • 也称为完整性规则，是数据库中的数据必须满足的语义约束条件
  • 完整性包括关系模型的实体完整性、参照完整性和用户定义完整性
  • 这些完整性一般由SQL的数据定义语言语句来实现
• 提供完整性检查机制
  • 检查数据是否满足完整性约束条件的机制称为完整性检查
  • 一般在INSERT、UPDATE、DELETE语句执行后开始检查，也可以在事务提交时检查
• 违约处理
  • 数据库管理系统若发现用户的操作违背了完整性约束条件，就采取一定的动作
    • 拒绝执行该操作（NO ACTION）
    • 级连执行其他操作（CASCADE）

由DBMS进行完整性检查的好处：

• 不必由应用程序来完成，从而减轻了应用程序员的负担
• 能够为所有的用户和应用提供一致的数据库完整性，避免出现漏洞

5.1 实体完整性

5.1.1 实体完整性定义

• 单属性构成的码可以定义为列级或表级
• 多个属性构成的码只能定义为表级

Ex：将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) /*表级定义主码*/
);

Ex：将SC表中的Sno，Cno属性组定义为码（多属性）

CREATE TABLE SC( 
	Sno CHAR(9) NOT NULL,
	Cno CHAR(4) NOT NULL,
	Grade SMALLINT,
	PRIMARY KEY (Sno, Cno) /*只能在表级定义主码*/
);

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

插入或对主码列进行更新操作，DBMS会按照实体完整性规则自动进行检查

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

检查记录中主码值是否唯一的一种方法是进行全表扫描

• 依次判断表中每一条记录的主码值与将插入记录上的主码值（或者修改的新主码值）是否相同
  
• 全表扫描缺点：十分耗时
• 为避免对基本表进行全表扫描，RDBMS核心一般都在主码上自动建立一个索引

例：新插入记录的主码值是25

5.2 参照完整性

例：学生关系的“专业号”是外码，它参照专业关系的主码“专业号”
学生关系中每个元组的“专业号”属性只取两类值：
(1) 空值，表示该学生尚未确定专业
(2)非空值，该值必须是专业关系中某个元组的“专业号”值，表示该学生不可能属于一个不存在的专业

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

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

5.2.1 参照完整性定义

例：Student表的Sdept属性是外码，参照DEPT表的主码Deptno
(1) 在列级定义参照完整性

CREATE TABLE Student(
	Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/
	Sname CHAR(20) UNIQUE,
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20) FOREIGN KEY REFERENCES DEPT(Deptno)
	/*在列级定义参照完整性*/
);

(2) 在表级定义参照完整性

CREATE TABLE Student(
	Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/
	Sname CHAR(20) UNIQUE,
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20),
	FOREIGN KEY(Sdept) REFERENCES DEPT(Deptno)
	/*在表级定义参照完整性*/
);

例：关系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, Cno), /*在表级定义实体完整性*/
	FOREIGN KEY (Sno) REFERENCES Student(Sno), /*在表级定义参照完整性*/
	FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表级定义参照完整性*/
);

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

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

参照完整性检查的时机？
例：表SC和Student有四种可能破坏参照完整性的情况
(1) SC表中增加一个元组，该元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等

(2) 修改SC表中的一个元组，修改后该元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等

(3) 从Student表中删除一个元组，造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等

(4) 修改Student表中一个元组的Sno属性，造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等 。

参照完整性违约处理

1. 拒绝执行 NO ACTION
   不允许该操作执行。该策略一般设置为默认策略
2. 级联操作 CASCADE
   当删除或修改被参照表（Student）的一个元组造成了与参照表（SC）的不一致，则删除或修改参照表中的所有造成不一致的元组
3. 设置为空值 SET-NULL
   当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。

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

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表中相应的元组*/
);

小结

• 参照完整性的定义方法
  • CREATE TABLE … FOREIGN KEY … REFERENCE
• 参照完整性的检查时机
  • 对被参照表和参照表进行增删改操作时
• 参照完整性的违约处理
  • 拒绝执行 NO ACTION
  • 级联操作 CASCADE
  • 设置为空值 SET-NULL

5.3 用户定义的完整性

用户定义的完整性是：针对某一具体应用的数据必须满足的语义要求
关系数据库管理系统提供了定义和检验用户定义完整性的机制，不必由应用程序承担

5.3.1 属性上的约束条件

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

• 列值非空 NOT NULL
• 列值唯一 UNIQUE
• 检查列值是否满足一个条件表达式 CHECK

不允许取空值

例：在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

CREATE TABLE SC( 
	Sno CHAR(9) NOT NULL,/*不允许取空值*/
	Cno CHAR(4) NOT NULL,/*不允许取空值*/
	Grade SMALLINT NOT NULL,/*不允许取空值*/
	PRIMARY KEY (Sno, Cno),
	…
);

列值唯一

例：建立专业表DEPT，要求专业名称Dname列取值唯一且不能取空值，专业编号Deptno列为主码。

CREATE TABLE DEPT(
	Deptno NUMERIC(2),
	Dname CHAR(9) UNIQUE NOT NULL，
	/*要求Dname列值唯一, 并且不能取空值*/
	PRIMARY KEY (Deptno)
);

用CHECK短语指定列值应该满足的条件

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

例：SC表的Grade的值应该在0和100之间。

CREATE TABLE SC( 
	Sno CHAR(9) ,
	Cno CHAR(4),
	Grade SMALLINT CHECK (Grade>=0 AND Grade <=100)， /*Grade取值范围是0到100*/
	PRIMARY KEY (Sno,Cno),
	FOREIGN KEY (Sno) REFERENCES Student(Sno),
	FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

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

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

5.3.2 元组上的约束条件

属性上的约束条件：只涉及单个属性

• 元组级的限制：可以设置不同属性之间的取值的相互约束条件
• 在CREATE TABLE时可以用CHECK子句定义元组上的约束条件

例：当学生的性别是男时，其名字不能以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.%') 
	/*定义了元组中Sname和Ssex两个属性值之间的约束条件*/ 
);

性别是女性的元组都能通过该项检查，因为Ssex='女'成立;
当性别是男性时，要通过检查则名字一定不能以Ms.打头

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

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

小结

• 属性上的用户定义完整性
  • 定义方法
  • 检查时机
  • 违约处理
• 元组上的用户定义完整性
  • 定义方法
  • 检查时机
  • 违约处理

5.4 完整性约束命名字句

CONSTRAINT <完整性约束条件名><完整性约束条件>

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

例：建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

CREATE TABLE Student( 
	Sno NUMERIC(6)
	CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
	Sname CHAR(20) CONSTRAINT C2 NOT NULL,
	Sage NUMERIC(3) CONSTRAINT C3 CHECK (Sage < 30),
	Ssex CHAR(2) CONSTRAINT C4 CHECK (Ssex IN ( ‘男’,'女')),
	CONSTRAINT StudentKey PRIMARY KEY(Sno)
);

例：建立教师表TEACHER，要求每个教师的应发工资不低于3000元。应发工资是工资列Sal与扣除项Deduct之和。

CREATE TABLE TEACHER( 
	Eno NUMERIC(4) PRIMARY KEY /*在列级定义主码*/
	Ename CHAR(10),
	Job CHAR(8),
	Sal NUMERIC(7,2),
	Deduct NUMERIC(7,2),
	Deptno NUMERIC(2),
	CONSTRAINT TEACHERFKey FOREIGN KEY (Deptno) REFERENCES DEPT(Deptno),
	CONSTRAINT C1 CHECK (Sal + Deduct >= 3000)
);

修改表中的完整性限制

使用ALTER TABLE语句修改表中的完整性限制

例：去掉上上例中Student表中对性别的限制。

ALTER TABLE Student
DROP CONSTRAINT C4;

例：修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40

• 可以先删除原来的约束条件，再增加新的约束条件：

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

ALTER TABLE Student
DROP CONSTRAINT C3;
ALTER TABLE Student
ADD CONSTRAINT C3 CHECK(Sage < 40);

域约束：CREATE DOMAIN 语句(含CHECK子句)

定义：

CREATE DOMAIN COLOR CHAR(6) DEFAULT '???'
CHECK(
	/*格式同SELECT的WHERE*/
	VALUE IN('Red','Yellow','Blue','Green', '???')
);

使用：

CREATE TABLE PART (
	pno char(5),
	pname varchar(20),
	partcolor COLOR,
	…… 
);

*5.5 域中的完整性限制

*5.6 断言

*5.7 触发器