(数据库系统概论|王珊)第五章数据库完整性-第一、二、三节:数据库三大完整性

文章目录

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

一:数据库完整性概述

(1)数据库完整性的基本概念

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

  • 正确性:数据是符合现实世界语义、反映当前实际状况的。例如性别只能是男或女
  • 相容性:是指数据库同一对象在不同关系表中的数据是符合逻辑的。比如说年龄一般都在1-100岁,当然也有超过一百岁的,反正没有两百岁,三百岁成仙的人类

(2)数据库完整性和安全性的区别

完整性:是为了防止数据库中存在不符合语义的数据,也就是防止数据库中存在不正确的数据。因此,完整性检查和控制的防范对象是不合语义的、不正确的数据,防止它们进入数据库

安全性:是保护数据库防止恶意破坏和非法存取。因此,安全性控制的防范对象是非法用户和非法操作,防止他们对数据库数据的非法存取

(3)为维护完整性DBMS必须要实现的功能

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

  • 完整性约束条件:是数据库中数据必须满足的语义条件规则
  • SQL标准使用了一系列概念来描述完整性,包括关系模型的实体完整性、参照完整性和用户定义完整性

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

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

3.进行违约处理

二:实体完整性

实体完整性:若属性A是基本关系 R R 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.开头
上一篇:Python编程挑战100题:11怎样实现学生成绩排序


下一篇:一个HTTPS转HTTP的Bug,他们竟然忍了2年?原谅我无法接受,加班改了!