第5章-数据库完整性

三、 用户定义的完整性
关系模型的用户定义的完整性定义
用户定义的完整性就是针对某一具体应用的数据必须满足的语义 要求 DBMS提供，而不必由应用程序承担 CREATE TABLE时定义
列值非空（NOT NULL）
列值唯一（UNIQUE） 检查列值是否满足一个布尔表达式（CHECK）
《数据库原理》课程之
数据库完整性
Database integrity
河南科技大学 信息工程学院 主讲人：杨春蕾
DBMS需保证数据库的
完整性
什么是数据库完整性？
数据的正确性 和 相容性
实体完整性 参照完整性 用户定义的完整性 实体唯一、可相互区分 可靠的数据来源 满足数据语义
为了更好地理解这三个数据完整性，我们一起来看几个例子 ，观察一下在没有完整性约束下，进行数据更新时可能会产 生什么样的问题？
DBMS需提供的数据库完整性功能
为维护数据库的完整性，DBMS必须： 1.提供定义完整性约束条件的机制 2.提供完整性检查的方法
3.违约处理
一、实体完整性
关系模型的实体完整性定义
CREATE TABLE中用PRIMARY KEY定义
(1)在列级定义主码
CREATE TABLE Student_pk (Sno CHAR(9) PRIMARY KEY， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT，
三、 用户定义的完整性
关系模型的用户定义的完整性检查和违约处理定义
插入元组或修改属性的值时，DBMS检查约束条件是否被满足 如果不满足则操作被拒绝执行
下面咱们来看两个用户定义的完整性的例子。用CREATE TABEL创建一个新的学生信息表Student_uk并插入原始数 据。在学生表中添加三个用户定义的完整性约束：姓名取值 唯一，年龄在16-28岁之间，还有一个元组级的CHECK约束 “性别为女，或姓名不以Ms打头”。那么先执行“INSERT INTO Student_uk VALUES (‘201215124’, ‘李勇’, ‘男’, 30, ‘CS’);” 会因姓名重复和年龄超出范围被拒绝插入，再执行 “INSERT INTO Student_uk VALUES (‘201215124’, ‘Ms.Zhang’, ‘男’, 20, ‘CS’);”会因CHECK约束拒绝姓名以 Ms打头的男生信息插入学生表。回到学生表中，数据也没 有发生更新。
三、 用户定义的完整性
4. 用CHECK短语指定元组应该满足的条件
P164［例5.9］ 当学生的性别是男时，其名字不能以 Ms.打头 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(8) NOT NULL， 定义了元组中Sname Ssex CHAR(2)， 和 Ssex两个属性值 Sage SMALLINT， 之间的约束条件 Sdept CHAR(20)， PRIMARY KEY (Sno)， CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%') )；
三、 用户定义的完整性
3. 用CHECK短语指定列值应该满足的条件
P164［例5.7］ Student表的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) ); 在性别属性Ssex 只允许取'男'或'女'
二、 参照完整性
关系模型的参照完整性定义
在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码
用REFERENCES短语指明这些外码参照哪些表的主码
二、参照完整性
例如，关系SC中一个元组表示一个学生选修的某门课程的成绩， （Sno，Cno）是主码。Sno，Cno分别参照引用Student表的主码和 Course表的主码
二、参照完整性
CREATE TABLE SC_fk (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY（Sno，Cno）， FOREIGN KEY (Sno) REFERENCES Student_pk(Sno) ON DELETE CASCADE /*级联删除SC表中相应的元组*/ ON UPDATE CASCADE， /*级联更新SC表中相应的元组*/ FOREIGN KEY (Cno) REFERENCES Course_pk(Cno) ON DELETE NO ACTION /*当删除Course 表中的元组造成了与SC表不一致时拒绝删除 */ ON UPDATE CASCADE /*当更新Course表中的Cno时，级联更新SC表中相应的元组*/ )；
违约处理 拒绝 拒绝 拒绝/级连删除/设置为空 值 拒绝/级连修改/设置为空 值
二、参照完整性
参照完整性违约处理
1. 拒绝(NO ACTION)执行 • 默认策略 2. 级联(CASCADE)操作 3. 设置为空值（SET-NULL） • 对于参照完整性，除了应该定义外码，还 应定义外码列是否允许空值
一、实体完整性
关系模型的实体完整性检查和违约处理
插入新数据或对主码列进行更新操作时，DBMS按照实体完整
性规则自动进行检查。包括：
1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改 2. 检查主码的各个属性是否为空，只要有一个为空就拒绝 插入或修改
一、实体完整性
检查记录中主码值是否唯一的一种方法是进行全表扫描
三、 用户定义的完整性
1.不允许取空值 2.列值唯一
CREATE TABLE SC CREATE TABLE DEPT （Sno CHAR(9) ， (Deptno NUMERIC(2)， Cno CHAR(4) ， Dname CHAR(9) UNIQUE， Grade SMALLINT NOT NULL， Location CHAR(10)， PRIMARY KEY (Sno， Cno)， PRIMARY KEY (Deptno) ）； )；
接下来，我们按照刚才设定参照完整性的方法，重新在数据库中定义选课表SC，选中CREATE TABLE 语句执行，可以在左侧的树形目录中看到，SC_fk这个基本表已被建立，且它的外键也已经 被创建。接着，我们试图再次向选课表中插入数据（‘201215124’,’9’,-77），在前面的演示中，当未 设置外码保证参照完整性时，这样一个表达“一个不存在的学生选修了一门不存在的课程”的数据 是能够进入数据库的。定义SC表的外码约束及违约处理之后，执行该Insert语句，我们会发现消息提 示“”。 接下来请大家注意观察外码约束中CASCADE和NO ACTION的作用。在创建选课表的外键约束时， 有违约处理“ON DELETE CASCADE”和“ON UPDATE CASCADE”，我们先来观察学生表中的 数据：121,、122、123和125四个学生，再来观察选课表中的数据：121选了三门课，122选了两门 课。那么当执行“DELETE FROM Student_pk WHERE Sno=‘201215122’; ”时，删除的学生 ‘201215122’基本信息就会涉及到选课表中该学生的选课信息将不再有学生信息来源，那么执行该 delete语句，DBMS将会提示“1行受影响”，正确执行，回到学生表中，可以看到201215122这位 同学的信息已被删除，再回到选课表中，我们能够发现122这位同学的两条选课信息也被删除掉。这 就是外键约束中“ON DELETE CASCADE”的作用。类似地，由于“ON UPDATE CASCADE”的 级联更新作用，当执行”UPDATE Student_pk SET Sno=‘201715121‘ WHERE Sno=’201215121’; “ 时，学生表中李勇的学号就从201215121修改为201715121，回到选课表中，可以看到，李勇的选 课信息中，他的学号也全都被改为201715121，这就是违约处理中级联更新的作用。 再来看关于课程表的两个例子。定义选课表中课程号Cno为外键时，有两个违约处理“ON DELETE NO ACTION”和“ON UPDATE CASCADE”，那么当执行“DELETE FROM Course_pk WHERE Cno=‘1’; ”时，我们可以看到DBMS的提示信息“delete语句与外键Cno冲突，语句已终止 ”，这就是“ON DELETE NO ACTION”的作用，拒绝删除，回到课程表中，1号课程并没有被删 除。而执行“UPDATE Course SET Cno=‘01‘ WHERE Cno=’1’; ”时，由于外键Cno上的违约处理 “ON UPDATE CASCADE”，能够看到课程表中的1号课程，课程号改为了01，而且在选课表中所 有的1号课程的课程号也被改为了01，这就是级联更新的作用。
二、参照完整性
被参照表Student
被参照表Course
参照表SC
二、参照完整性
关系模型的参照完整性检查和违约处理定义
可能破坏参照完整性的情况及违约处理
被参照表（例如Student ） 可能破坏参照完整性 可能破坏参照完整性 删除元组 修改主码值
参照表（例如SC） 插入元组 修改外码值 可能破坏参照完整 性 可能破坏参照完整 性
P160［例5.3］ 定义SC中的参照完整性 在表级定义 CREATE TABLE SC_fktemp (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) );
(2)在表级定义主码
CREATE TABLE TABLE Couse_pk SC_pk CREATE
Sdept CHAR(20));
(Sno CHAR(9) NOT (Cno CHAR(4) , NULL， Cno CHAR(4) NOT NULL ， Cname CHAR(40) NOT NULL, Grade SMALLINT， Cpno CHAR(4), PRIMARY KEY (Sno，Cno) Ccredit SMALLINT ， /*只能在表级定义主码*/ PRIMARY KEY (Cno) ); );
源自文库 一、实体完整性
B+树索引
当创建基本表的时候设置了主码，再插入或修改新的数据时有重 复主码或主码属性取值为空的情况，DBMS就会做出提示，并拒 绝数据的更新。我们重新定义学生基本信息表Student、课程表 Course和选课表SC，按照前面的例子分别设置Sno、Cno以及组 合码(Sno,Cno)为这三张表的主码，并按照课本79-80页的示例插 入数据。那么当再次向Student表中插入数据（‘201215121’,’王梅 ’,’女’,20,’CS’）时，点击执行按钮，DBMS会给出这样的消息“违 反了PRIMARY KEY约束，不能在对象dbo.Student中插入重复键 ”，说明实体完整性起作用 ，拒绝重复的主码进入数据库。再插 入数据（NULL,’李勇’,’男’,20,’CS’）点击执行按钮，DBMS会提示 “不能将值NULL插入列Sno，列不允许有空值，Insert失败，语句 已终止”，即主码不能为空。这种情况对于组合主码来说也是一 样的，当向SC表中插入数据（‘201215122,NULL,NULL）时，由 于DBMS提供的实体完整性保证，该数据仍然会因为主码不能为 空而拒绝数据插入。