@
目录在(数据库系统概论|王珊)第二章关系数据库-第二节、第三节:关系操作和关系完整性这一节中对数据库的三大完整性已有介绍,而本章的内容就是如何在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)
演示
CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT NOT NULL,
PRIMARY KEY(Sno,Cno)//定义了实体完整性,隐含了不允许取空值,在列级中可不写
);
B:列值唯一(UNIQUE)
演示
CREATE TABLE DEPT
(
Deptno NUMERIC(2),
Dname CHAR(9) UNIQUE NOT NULL, //唯一且不能取空值
Location CHAR(10),
PRIMARY KEY(Depto)
);
C:满足指定条件(CHECK)
演示
例如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)元组上的约束条件
同属性值限制相比,元组级的限制可以设置不同属性之间的取值的相互约束条件
演示
例如,规定插入男性时,其名字不能以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.
开头