DB05数据库教学省公共课一等奖全国赛课获奖课件.pptx

辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理第第5 5章章 数据库完整性数据库完整性本章重点：本章重点：1掌握数据库完整性约束定义、检验方法及违约处理掌握数据库完整性约束定义、检验方法及违约处理2掌握触发器定义及存放过程定义掌握触发器定义及存放过程定义3本章难点：本章难点：4 触发器定义及存放过程定义触发器定义及存放过程定义5了解内容：了解内容：6 域中完整性控制域中完整性控制第1页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理第第5 5章章 数据库完整性数据库完整性数据库完整性数据库完整性数据数据正确性正确性和和相容性相容性数据完整性和安全性是两个不一样概念数据完整性和安全性是两个不一样概念数据完整性数据完整性预防数据库中存在不符合语义数据，也就是预防数据库中预防数据库中存在不符合语义数据，也就是预防数据库中存在不正确数据存在不正确数据防范对象：不合语义、不正确数据防范对象：不合语义、不正确数据数据安全性数据安全性保护数据库预防恶意破坏和非法存取保护数据库预防恶意破坏和非法存取防范对象：非法用户和非法操作防范对象：非法用户和非法操作第2页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理第第5 5章章 数据库完整性数据库完整性为维护数据库完整性，为维护数据库完整性，DBMS必须：必须：1.提供定义完整性约束条件机制提供定义完整性约束条件机制2.提供完整性检验方法提供完整性检验方法3.违约处理违约处理第3页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理&实体完整性实体完整性&参考完整性参考完整性&用户定义完整性用户定义完整性&完整性约束命名字句完整性约束命名字句&触发器触发器&存放过程存放过程第第5 5章章 数据库完整性数据库完整性第4页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.1 5.1 实体完整性实体完整性实体完整性定义实体完整性定义实体完整性检验和违约处理实体完整性检验和违约处理第5页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.1.1 5.1.1 实体完整性定义实体完整性定义关系模型实体完整性关系模型实体完整性CREATE TABLECREATE TABLE中用中用PRIMARY KEYPRIMARY KEY定义定义单属性组成码有两种说明方法单属性组成码有两种说明方法 定义为列级约束条件定义为列级约束条件定义为表级约束条件定义为表级约束条件对多个属性组成码只有一个说明方法对多个属性组成码只有一个说明方法定义为表级约束条件定义为表级约束条件 第6页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.1.1 5.1.1 实体完整性定义实体完整性定义例例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);第7页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理(2)在表级定义主码 CREATE TABLE Student (Sno CHAR(9)，Sname CHAR(20)NOT NULL，Ssex CHAR(2)，Sage SMALLINT，Sdept CHAR(20)，PRIMARY KEY(Sno);5.1.1 5.1.1 实体完整性定义实体完整性定义第8页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.1.1 5.1.1 实体完整性定义实体完整性定义例例2 将将SC表中表中Sno，Cno属性组定义为码属性组定义为码 CREATE TABLE SC (Sno CHAR(9)NOT NULL，Cno CHAR(4)NOT NULL，Grade SMALLINT，PRIMARY KEY(Sno，Cno)/*只能在表级定只能在表级定义主码义主码*/);第9页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.1.2 5.1.2 实体完整性检验和违约处理实体完整性检验和违约处理插入或对主码列进行更新操作时，插入或对主码列进行更新操作时，RDBMS按照实按照实体完整性规则自动进行检验。包含：体完整性规则自动进行检验。包含：1.检验主码值是否唯一，假如不唯一则拒绝插入或检验主码值是否唯一，假如不唯一则拒绝插入或修改修改2.检验主码各个属性是否为空，只要有一个为空就检验主码各个属性是否为空，只要有一个为空就拒绝插入或修改拒绝插入或修改第10页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理检验统计中主码值是否唯一一个方法是进行检验统计中主码值是否唯一一个方法是进行全表全表扫描扫描5.1.2 5.1.2 实体完整性检验和违约处理实体完整性检验和违约处理第11页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.1.2 5.1.2 实体完整性检验和违约处理实体完整性检验和违约处理索引索引 第12页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.2 5.2 参考完整性参考完整性参考完整性定义参考完整性定义参考完整性检验和违约处理参考完整性检验和违约处理第13页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.2.1 5.2.1 参考完整性定义参考完整性定义关系模型参考完整性定义关系模型参考完整性定义在在CREATE TABLE中用中用FOREIGN KEY短语定短语定义哪些列为外码义哪些列为外码用用REFERENCES短语指明这些外码参考哪些表短语指明这些外码参考哪些表主码主码 第14页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理例例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)/*在表级定义参考完整性在表级定义参考完整性*/);5.2.1 5.2.1 参考完整性定义参考完整性定义第15页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.2.2 5.2.2 参考完整性检验和违约处理参考完整性检验和违约处理可能破坏参考完整性情况及违约处理可能破坏参考完整性情况及违约处理被参考表（比如被参考表（比如Student）参考表（比如参考表（比如SC）违约处理违约处理可能破坏参考完整性可能破坏参考完整性 插入元组插入元组拒绝拒绝可能破坏参考完整性可能破坏参考完整性 修改外码值修改外码值拒绝拒绝删除元组删除元组 可能破坏参考完整可能破坏参考完整性性拒绝拒绝/级连删除级连删除/设置为空值设置为空值修改主码值修改主码值 可能破坏参考完整可能破坏参考完整性性拒绝拒绝/级连修改级连修改/设置为空值设置为空值第16页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理参考完整性违约处理参考完整性违约处理1.拒绝拒绝(NO ACTION)执行执行默认策略默认策略2.级联级联(CASCADE)操作操作3.设置为空值（设置为空值（SET-NULL）对于参考完整性，除了应该定义外码，还应定对于参考完整性，除了应该定义外码，还应定义义外码列是否允许空值外码列是否允许空值5.2.2 5.2.2 参考完整性检验和违约处理参考完整性检验和违约处理第17页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理例例3 显式说明参考完整性违约处理示例显式说明参考完整性违约处理示例 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表中对应元组*/)；5.2.2 5.2.2 参考完整性检验和违约处理参考完整性检验和违约处理第18页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.3 5.3 用户定义完整性用户定义完整性用户定义完整性就是针对用户定义完整性就是针对某一详细应用某一详细应用数据必须数据必须满足语义要求满足语义要求 RDBMS提供，而无须由应用程序负担提供，而无须由应用程序负担第19页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.3.1 5.3.1 属性上约束条件定义属性上约束条件定义CREATE TABLE时定义时定义列值非空（列值非空（NOT NULL）列值唯一（列值唯一（UNIQUE）检验列值是否满足一个布尔表示式（检验列值是否满足一个布尔表示式（CHECK）第20页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.3.1 5.3.1 属性上约束条件定义属性上约束条件定义1.不允许取空值不允许取空值 例例5 在定义在定义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)，/*假如在表级定义实体完整性，隐含了假如在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值定义就无不允许取空值，则在列级不允许取空值定义就无须写了须写了*/）；）；第21页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理2.列值唯一列值唯一 例例6 建立部门表建立部门表DEPT，要求部门名称，要求部门名称Dname列取值列取值唯一，部门编号唯一，部门编号Deptno列为主码列为主码 CREATE TABLE DEPT (Deptno NUMERIC(2)，Dname CHAR(9)UNIQUE，/*要求要求Dname列值列值唯一唯一*/Location CHAR(10)，PRIMARY KEY(Deptno)；5.3.1 5.3.1 属性上约束条件定义属性上约束条件定义第22页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理3.用用CHECK短语指定列值应该满足条件短语指定列值应该满足条件例例7 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);5.3.1 5.3.1 属性上约束条件定义属性上约束条件定义第23页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.3.1 5.3.1 属性上约束条件定义属性上约束条件定义例例8 SC表表Grade值应在值应在0到到100之间之间 CREATE TABLE SC (Sno CHAR(9)NOT NULL，Cno CHAR(4)NOT NULL，Grade SMALLINT Check(Grade=0 and Grade=100),PRIMARY KEY(Sno，Cno),/*在表级定义实体完整性在表级定义实体完整性*/FOREIGN KEY(Sno)REFERENCES Student(Sno)，FOREIGN KEY(Cno)REFERENCES Course(Cno);第24页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.3.2 5.3.2 属性上约束条件检验和违约处理属性上约束条件检验和违约处理插入元组或修改属性值时，插入元组或修改属性值时，RDBMS检验属性上约检验属性上约束条件是否被满足束条件是否被满足假如不满足则操作被拒绝执行假如不满足则操作被拒绝执行 第25页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.3.3 5.3.3 元组上约束条件定义元组上约束条件定义例例9 当学生性别是男时，其名字不能以当学生性别是男时，其名字不能以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.打头打头第26页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.3.4 5.3.4 元组上约束条件检验和违约处理元组上约束条件检验和违约处理插入元组或修改属性值时，插入元组或修改属性值时，RDBMS检验元组上约检验元组上约束条件是否被满足束条件是否被满足假如不满足则操作被拒绝执行假如不满足则操作被拒绝执行 第27页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.4 5.4 完整性约束命名子句完整性约束命名子句1.CONSTRAINT 约束约束子句子句CONSTRAINT PRIMARY KEY短语短语|FOREIGN KEY短语短语|CHECK短语短语第28页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.4 5.4 完整性约束命名子句完整性约束命名子句例例10 建立学生记录表建立学生记录表Student，要求学号在，要求学号在9000099999之间，姓名不能取空值，年纪小于之间，姓名不能取空值，年纪小于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)；在在Student表上建立了表上建立了5个约束条件，包含主码约束（命名为个约束条件，包含主码约束（命名为StudentKey）以及）以及C1、C2、C3、C4四个列级约束。四个列级约束。第29页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理2.修改表中完整性限制修改表中完整性限制使用使用ALTER TABLE语句修改表中完整性限制语句修改表中完整性限制5.4 5.4 完整性约束命名子句完整性约束命名子句例例12 去掉去掉例例10 student表中对性别限制表中对性别限制 ALTER TABLE Student DROP CONSTRAINT C4第30页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理例例13 修改表修改表Student中约束条件，要求学号改为在中约束条件，要求学号改为在900000999999之间，年纪由小于之间，年纪由小于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)；5.4 5.4 完整性约束命名子句完整性约束命名子句第31页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.5 5.5 触发器触发器触发器（触发器（Trigger）是用户定义在关系表上一类由）是用户定义在关系表上一类由事件驱动事件驱动特殊过程特殊过程由服务器自动激活由服务器自动激活能够进行更为复杂检验和操作，含有更精细和更能够进行更为复杂检验和操作，含有更精细和更强大数据控制能力强大数据控制能力 第32页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理SnoSnameSsex95001张勇张勇男男95002赵楠赵楠男男95003宋佳宋佳女女Student表CnoCnameCpno1计算机基计算机基础础2C语言语言13软件工程软件工程4Course表SnoCnoScore950011 90950022 85SC表应用实例应用实例5.5 5.5 触发器触发器第33页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.5.1 5.5.1 定义触发器定义触发器CREATE TRIGGER语法格式语法格式CREATE TRIGGER 触发器名称触发器名称ON 表名（触发器定义在哪个表上）表名（触发器定义在哪个表上）FOR DELETE,INSERT,UPDATE AS BEGIN触发体触发体END第34页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理定义触发器语法说明定义触发器语法说明:1.触发器名触发器名2.表名：触发器目标表表名：触发器目标表3.触发事件：触发事件：INSERT、DELETE、UPDATE5.5.1 5.5.1 定义触发器定义触发器第35页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理比如比如,假设在假设在Student表上创建了一个表上创建了一个UPDATE触发触发器。器。CREATE TRIGGER update_sno ON Student FOR updateAS if update(sno)begin update sc set sno=c.sno from deleted b,inserted c where sc.sno=b.sno end5.5.1 5.5.1 定义触发器定义触发器第36页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理4.触发条件触发条件触发条件为真触发条件为真省略省略WHERE触发条件触发条件5.触发动作体触发动作体触发动作体能够是一个匿名触发动作体能够是一个匿名PL/SQL过程块过程块也能够是对已创建存放过程调用也能够是对已创建存放过程调用5.5.1 5.5.1 定义触发器定义触发器第37页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理插入视图和删除视图插入视图和删除视图(SQL SERVER)当在定义了触发器表上发生修改操作时，会派生出两个视当在定义了触发器表上发生修改操作时，会派生出两个视图，一个是插入视图，一个是删除视图。图，一个是插入视图，一个是删除视图。当在表上发生插入操作时，新插入行将出现在插入视图当在表上发生插入操作时，新插入行将出现在插入视图中；中；当在表上发生删除操作时，被删除行将出现在删除视图当在表上发生删除操作时，被删除行将出现在删除视图中；中；当在表上发生更新操作时，旧行出现在删除视图中，而当在表上发生更新操作时，旧行出现在删除视图中，而新行出现在插入视图中。（更新实现过程是先删除旧行，然新行出现在插入视图中。（更新实现过程是先删除旧行，然后插入新行）后插入新行）这里插入视图和删除视图只在触发器内可用，一旦触发器这里插入视图和删除视图只在触发器内可用，一旦触发器完成任务，这两个视图将不再存在。完成任务，这两个视图将不再存在。这两个视图名称分别是这两个视图名称分别是insterted和和deleted，他们和原表，他们和原表含有完全相同结构。含有完全相同结构。5.5.1 5.5.1 定义触发器定义触发器第38页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理假如从假如从studentstudent表中删除学生基本信息，则从表中删除学生基本信息，则从SCSC中删中删除全部该学生成绩信息。除全部该学生成绩信息。CREATE TRIGGER delete_sno ON Student FOR deleteAS begin delete sc from sc,deleted a where sc.sno=b.sno end第39页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理假如从假如从studentstudent表中插入学生基本信息，则从表中插入学生基本信息，则从student1student1中插入该学生信息。中插入该学生信息。CREATE TRIGGER insert_sno ON Student FOR insertAS begin insert student1(sno)select c.sno from inserted c end第40页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.2.2 5.2.2 激活触发器激活触发器触发器执行，是由触发器执行，是由触发事件激活触发事件激活，并由数据库服，并由数据库服务器自动执行务器自动执行一个数据表上可能定义了一个数据表上可能定义了多个触发器多个触发器第41页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.5.3 5.5.3 删除触发器删除触发器删除触发器删除触发器SQL语法：语法：DROP TRIGGER ON;触发器必须是一个已经创建触发器，而且只能由触发器必须是一个已经创建触发器，而且只能由含有对应权限用户删除。含有对应权限用户删除。例例21 删除教师表删除教师表student上触发器上触发器Insert_sno DROP TRIGGER Insert_sno ON student第42页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.6 存放过程存放过程CREATE PROCEDURE P_Sno ls_sno varchar(6)AS BEGIN TRANSACTION BEGIN select sno,sname,ssex from student where sno=:ls_sno;END COMMIT TRANSACTION;5.6.1 存放过程定义存放过程定义第43页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.6.2 存放过程执行存放过程执行 exec P_Sno 950015.6.3 存放过程删除存放过程删除 drop procedure p_sno第44页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理5.7 5.7 小结小结数据库完整性是为了确保数据库中存放数据是正数据库完整性是为了确保数据库中存放数据是正确确RDBMS完整性实现机制完整性实现机制完整性约束定义机制完整性约束定义机制完整性检验机制完整性检验机制违反完整性约束条件时违反完整性约束条件时RDBMS应采取动作应采取动作第45页辽宁工程技术大学电子与信息工程学院辽宁工程技术大学电子与信息工程学院 数据库原理数据库原理作业：作业：P164 3、6、7第46页