数据库原理 Database Principles 第五章 数据库完整性 Database Principles

第五章 数据库完整性 教学内容 实体完整性 参照完整性 Database Principles

第五章 数据库完整性 教学目标 认知目标： 了解什么是数据库的完整性约束条件；完整性约束条件的分类；数据库的完整性与数据库的安全性概念的区别。 理解完整性约束的内容。 能力目标: 掌握实体完整性约束条件和参照完整性约束条件的定义、检查和违约反应。 Database Principles

第五章 数据库完整性 重点难点 重点： 实体完整性约束条件和参照完整性约束条件的定义、实体完整性约束条件和参照完整性约束条件的检查和违约反应；参照完整性的实现机制 难点： 参照完整性的实现机制 Database Principles

复习 数据模型的组成要素 数据结构 数据操作 完整性约束条件 Database Principles

数据库完整性 数据库的完整性 数据的正确性和相容性。 数据的完整性和安全性是两个不同概念 数据的完整性 防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据 防范对象：不合语义的、不正确的数据 数据的安全性 保护数据库防止恶意的破坏和非法的存取 防范对象：非法用户和非法操作 Database Principles

为维护数据库的完整性，DBMS 的完整性控制机制必须具有三方面的功能： 1.提供定义完整性约束条件（完整性规则）的机制 2.提供完整性检查的方法 3.违约处理 Database Principles

5.1 实体完整性 5.1.1 实体完整性定义 关系模型的实体完整性 若属性A是基本关系R的主属性，则属性A 不能取空值。 5.1 实体完整性 5.1.1 实体完整性定义 关系模型的实体完整性 若属性A是基本关系R的主属性，则属性A 不能取空值。 在CREATE TABLE中用PRIMARY KEY定义。 Database Principles

学生-课程数据库 学生-课程数据库 : 学生表：Student(Sno,Sname,Ssex,Sage,Sdept) 课程表：Course(Cno,Cname,Cpno,Ccredit) 学生选课表：SC(Sno,Cno,Grade) Database Principles

Student表 字段名 字段类型 宽度 备注 Sno 字符 9 学生学号 Sname 20 学生姓名 Ssex 2 学生性别 Sage 备注  Sno 字符 9 学生学号 Sname 20 学生姓名  Ssex 2 学生性别 Sage 短整型   学生年龄  Sdept 学生所在系  Database Principles

Course表 字段名 字段类型 宽度 备注 Cno 字符 4 课程编号 Cname 40 课程名 Cpno 先行课 Ccredit 短整型 备注  Cno 字符 4 课程编号 Cname 40 课程名  Cpno 先行课 Ccredit 短整型   学分  Database Principles

SC表 字段名 字段类型 宽度 备注 Sno 字符 9 学生学号 Cno 4 课程编号 Grade 短整型 选课成绩 备注  Sno 字符 9 学生学号 Cno 4 课程编号  Grade 短整型 选课成绩 Database Principles

(Sno CHAR(9) PRIMARY KEY， /*在列级定义主码*/ Sname CHAR(20) NOT NULL， ［例1］ 将Student表中的Sno属性定义为码 （1）在列级定义主码 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， /*在列级定义主码*/ Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)); Database Principles

CREATE TABLE Student （2）在表级定义主码 (Sno CHAR(9)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno) /*在表级定义主码*/ ); Database Principles

【练习】 将Course表中的Cno属性定义为码 CREATE TABLE Course （Cno CHAR(4) ， Cname CHAR(40)， Cpno CHAR(4) ， Ccredit SMALLINT， PRIMARY KEY（Cno） /*在表级定义主码*/ ）； Database Principles

PRIMARY KEY (Sno，Cno) /* 主码由两个属性构成，只能在表 级定义主码*/ ); ［例2］将SC表中的Sno，Cno属性组定义为码 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno，Cno) /* 主码由两个属性构成，只能在表 级定义主码*/ ); Database Principles

插入一条记录或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括： 5.1.2 实体完整性检查和违约处理 插入一条记录或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括： 1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改 2. 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改 Database Principles

实体完整性检查和违约处理 检查记录中主码值是否唯一的一种方法是进行全表扫描。依次判断表中每一条记录的主码值与将插入记录上的主码值是否相同。 Database Principles

实体完整性检查和违约处理 索引 Database Principles

5.2 参照完整性 5.2.1 参照完整性定义 若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码Ks相对应（基本关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须为：  或者取空值（F的每个属性值均为空值）  或者等于S中某个元组的主码值。 Database Principles

实现参照完整性： 系统提供定义外码的机制 定义外码列是否允许空值的机制 实现参照完整性： 系统提供定义外码的机制 定义外码列是否允许空值的机制 Database Principles

在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码 关系模型的参照完整性定义 在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码 用REFERENCES短语指明这些外码参照哪些表的主码 对于参照完整性，除了应该定义外码，还应该定义外码列是否允许空值。 Database Principles

PRIMARY KEY （Sno， Cno）， /*在表级定义实体完整性*/ ［例3］ 定义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） ）; Database Principles

5.2 参照完整性 5.2.2 参照完整性检查和违约处理 一个参照完整性将两个表中的相应元组联系起来了。因此，对被参照表和参照表进行增删改操作时可能破坏参照完整性，必须进行检查。 Database Principles

Student表 Sno Sname Ssex Sage Sdept 200215121 200215122 200215123 200515125 李勇 刘晨 王敏 张立 男 女 20 19 18 CS MA IS Database Principles

Course表 Cno Cname Cpno Ccredit 1 2 3 4 5 6 7 数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL语言 Database Principles

SC表 Sno Cno Grade 200215121 200215122 1 2 3 56 85 68 50 80 Database Principles

参照完整性检查和违约处理 可能破坏参照完整性的情况及违约处理 被参照表（例如Student） 参照表（例如SC） 违约处理 插入元组 拒绝 修改外码值 删除元组 拒绝/级连删除/设置为空值 修改主码值 拒绝/级连修改/设置为空值 Database Principles

违约处理 参照完整性违约处理 1. 拒绝(NO ACTION)执行 不允许该操作执行。 2. 级联(CASCADE)操作 3. 设置为空值 当删除或修改被参照表的一个元组造成与参照表的不一致，则删除或修改参照表的所有造成不一致的元组。 3. 设置为空值 当删除或修改被参照表的一个元组造成与参照表的不一致，则将参照表的所有造成不一致的元组的对应属性设置为空值。 Database Principles

外码是否能够取空值依赖于应用环境的语义 。 例如，下面两个关系 学生（学号，姓名，性别，专业号，年龄） 专业（专业号，专业名） 学生的专业号是外码，按照应用的实际情况可以取空值，表示尚未给该学生分配专业。 Database Principles

学生－课程数据库中，关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。Sno，Cno分别参照Student表的主码和Course表的主码。 Database Principles

因此， 对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值。 若SC的Sno为空值： 表明尚不存在的某个学生，或者某个不知学号的学生，选修了某门课程，其成绩记录在Grade中，这与学校的应用环境是不相符的，因此SC的Sno列不能取空值。 同样SC的Cno列不能取空值。 因此， 对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值。 Database Principles

当对参照表和被参照表的操作违反了参照完整性，系统选用默认策略，及拒绝执行。如果想让系统采用其他策略则必须在创建表时显式加以说明。 Database Principles

PRIMARY KEY（Sno，Cno）， FOREIGN KEY （Sno）REFERENCES Student（Sno） ［例4］ 显式说明参照完整性的违约处理示例 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表中相应的元组*/ )； Database Principles

【小结】 本节课介绍了实体完整性约束条件和参照完整性约束条件的定义、检查和违约反应，学生要会用SQL语言定义实体完整性约束条件和参照完整性约束条件。 Database Principles

【练习】 一、选择题 　　1．完整性检查和控制的防范对象（ ），防止它们进入数据库。安全性控制的防范对象是（ ），防止他们对数据库数据的存取。 　　A. 不合语义的数据 B. 非法用户 　　C. 不正确的数据 D. 非法操作 　　2．下述哪个是SQL语言中的数据控制命令（ ）。 　　A. GRANT B. COMMIT 　　C. UPDATE D. SELECT 　　3．下述SQL语言中的权限，哪一个允许用户定义新关系时，引用其他关系的主码作为外码（ ）。 　　A. INSERT B. DELETE 　　C. REFERENCES D. SELECT Database Principles

C. 可由一个或多个其值能唯一标识该关系模式中任何元组的属性组成 D. 以上都不是 4.关系模型中，一个码是（　　）。     A. 可由多个任意属性组成     B. 至多由一个属性组成    C. 可由一个或多个其值能唯一标识该关系模式中任何元组的属性组成    D. 以上都不是 5. 在关系R(R#, RN, S#)和 S(S#，SN, SD)中，R的主码是R#, S的主码是S#，则S#在R中称为（　　）。     A. 外码　　　　　　B. 候选码     C. 主码                     D. 超码 Database Principles

（2）将元组（‘SB0121’，‘BJDJ-500’，‘500W’，40）插入该表。 二、综合题 设有如下SB设备信息：     BH――设备编号(字符型,长度8)，XH――设备型号(字符型,长度10)，GL――功率(字符型,长度6)，SL――数量(整型)。要求：   （1）用SQL语言定义相应的基表，并定义关键字，规定功率数据项不能为空。 （2）将元组（‘SB0121’，‘BJDJ-500’，‘500W’，40）插入该表。 Database Principles

（BH CHAR（8）PRIMARY KEY， XH CHAR（10）， GL CHAR（6） NOT NULL， SL INT) 答： （1）CREATE TABLE SB （BH CHAR（8）PRIMARY KEY，                XH CHAR（10），                GL CHAR（6） NOT NULL，                SL INT) （2）INSERT INTO SB VALUES （‘SB0121’,‘BJDJ-500’,‘500W’,40)） Database Principles