WindowsServer数据库课件--第05章数据库完整性(3学时)new.pptVIP

An Introduction to Database System,1,数据库系统概论 An Introduction to Database System 第五章 数据库完整性,An Introduction to Database System,2,第五章 数据库完整性,什么是数据库的完整性? 数据的正确性和相容性 防止不合语义的、不正确的数据进入数据库。 例:学生的年龄必须是整数，取值在6-50间； 学生的性别只能是男或女； 学生的学号一定是唯一的； 学生所在的系必须是学校已有的系；,完整性：保证数据库能否真实地反映现实世界.,区别数据完整性和安全性的两个概念。,An Introduction to Database System,3,完整性约束条件是数据模型的组成部分，约束数据库中数据的语义。,检查用户发出的操作请求是否违背了完整性约束条件。,操作违背了完整性约束条件，则采取一定的操作来保证数据的完整性。,为保证完整性，DBMS必须做到： 1.完整性约束条件定义机制 2.完整性检查机制 3.违约反应,第五章 数据库完整性,An Introduction to Database System,4,第五章 数据库完整性,5.1 实体完整性 5.2 参照完整性 5.3 用户定义完整性 5.4 完整性约束命名子句 5.5 触发器,An Introduction to Database System,5,5.1 实体完整性,1.实体完整性定义 在CREATE TABLE语句中用PRIMARY KEY定义 码由单字段构成： 定义可以为列级或表级约束； 码由多字段构成： 定义只能为表级约束；,An Introduction to Database System,6,5.1 实体完整性,例1在数据库中，定义Student表的Sno属性为主码。 定义在列级约束： CREATE TABLE Student (Sno CHAR(8) , Sname CHAR(8), Sage INT);,定义在表级约束： CREATE TABLE Student (Sno CHAR(8)， Sname CHAR(8), Sage INT， );,PRIMARY KEY,PRIMARY KEY(Sno);,An Introduction to Database System,7,5.1 实体完整性,例2在SC表中定义(Sno,Cno)为主码。 CREATE TABLE SC ( Sno CHAR(5), Cno CHAR(2), Grade SMALLINT, PRIMARY KEY (Sno, Cno) );,An Introduction to Database System,8,5.1 实体完整性,2.实体完整性检查和违约处理 (1)完整性检查 当用户进行插入记录或修改主码时，DBMS系统自动检查： 检查主码值是否惟一； 检查每个主属性值是否为空。,(2)违约处理 拒绝插入或修改操作。,An Introduction to Database System,9,5.1 实体完整性,2.实体完整性检查和违约处理 (3)检查主码是否惟一的方法 全表扫描 依次判断表中每一个记录的主码值与插入记录的主码值是否一致。 缺点：效率低 利用主码的B索引 优点：效率高,An Introduction to Database System,10,5.1 实体完整性,25,51,12 30,66 78,3 7,15 20 25,30 41,51 54 65,68 69 71 76,79 84 93,An Introduction to Database System,11,第五章 数据库完整性,5.1 实体完整性 5.2 参照完整性 5.3 用户定义完整性 5.4 完整性约束命名子句 5.5 触发器,An Introduction to Database System,12,5.2 参照完整性,1.参照完整性定义 在CREATE TABLE语句中： 用 FOREIGN KEY 短语定义外码列； 用 REFERENCES 短语指明外码参照哪个表的主码。,An Introduction to Database System,13,5.2 参照完整性,例3在SC表中的参照完整性。 CREATE TABLE SC (Sno CHAR(8) NOT NULL, Cno CHAR(3) NOT NULL, Grade SMALLINT, PRIMARY KEY (Sno, Cno), FOREIGN KEY (Cno) REFERENCES course(Cno) ON DELETE CASCADE );,REFERENCES student(Sno),An Introduction to Database System,14,5.2 参照完整性,例3在SC表中的参照完整性。 ALTER TABLE SC ADD FOREIGN KEY (SNO) REFERENCES STUDENT(SNO),An Introduction to Database System,15,2. 参照完整性检查和违约处理 对被参照表和参照表进行增、删、改操作时进行检查,有四种情况可能破坏参照完整性： 参照表中 插入一个新元组； 修改外码值； 被参照表中 删除一个元组； 修改主码值。,5.2 参照完整性,An Introduction to Database System,16,当参照完整性不一致时，系统采取以下策略： 拒绝执行（默认策略） 级联操作（CASCADE） 当删除或修改被参照表的一个元组造成与参照表不一致时，则删除或修改参照表中所有不一致的元组。 设置为空（SET-NULL） 当删除或修改被参照表的一个元组造成与参照表不一致时，则将参照表中所有造成不一致的元组对应属性置空。,5.2 参照完整性,定义参照完整性时，除了定义外码，还应定义外码是否允许为空。,An Introduction to Database System,17,例：要删除Student关系中Sno=950001的元组，而SC关系中有4个元组的Sno都等于950001。,置空值删除： 将SC关系中所有Sno=950001的元组Sno置空。,级联删除： 将SC关系中所有4个Sno=950001的元组一起删除。依此类推可以继续级联下去。 受限删除： 系统将拒绝执行此删除操作。,5.2 参照完整性,An Introduction to Database System,18,第五章 数据库完整性,5.1 实体完整性 5.2 参照完整性 5.3 用户定义完整性 5.4 完整性约束命名子句 5.5 触发器,An Introduction to Database System,19,5.3 用户定义的完整性,1.属性上约束条件的定义 使用CREATE TABLE语句在定义属性的同时，定义对属性值的限制。 包括： 列值非空(NOT NULL) 列值唯一(UNIQUE) 检查列值是否满足一个布尔表达式(CHECK子句)。,An Introduction to Database System,20,5.3 用户定义的完整性,(1)不允许取空值,例5在定义SC表时，说明Sno、Cno属性不取空 CREATE TABLE SC ( Sno CHAR(5) NOT NULL, Cno CHAR(2) NOT NULL, Grade SMALLINT, PRIMARY KEY (Sno, Cno) );,An Introduction to Database System,21,5.3 用户定义的完整性,(2)列值惟一,例6建立部门表Dept，要求部门名称Dname惟一，部门编号为主码。 CREATE TABLE Dept ( Dno NUMERIC(2) NOT NULL, Dname CHAR(20) UNIQUE, Location CHAR(10), PRIMARY KEY (Dno) );,An Introduction to Database System,22,5.3 用户定义的完整性,(2)列值惟一,例6补充 若部门表Dept已建立，添加部门名称Dname惟一的约束。 ALTER TABLE Dept ADD UNIQUE(Dname);,An Introduction to Database System,23,5.3 用户定义的完整性,例7Student表中Ssex只允许取“男”和“女”。 CREATE TABLE Student ( Sno CHAR(5) PRIMARY KEY, Sname CHAR(8) NOT NULL, Ssex CHAR(2) CHECK (Ssex IN (男,女), Sage SMALLINT, Sdept CHAR(20) );,(3)用CHECK短语指定列值应该满足的条件,An Introduction to Database System,24,5.3 用户定义的完整性,例7补充 向Student表添加Ssex只许取“男”和“女”的约束。 ALTER TABLE Student ADD CHECK (Ssex IN (男,女);,(3)用CHECK短语指定列值应该满足的条件,An Introduction to Database System,25,5.3 用户定义的完整性,例8在定义SC表时，说明Grade应该在0和100之间。 CREATE TABLE SC ( Sno CHAR(5) NOT NULL, Cno CHAR(2) NOT NULL, Grade SMALLINT CHECK (Grade=0 AND Grade=100), PRIMARY KEY (Sno, Cno) );,(3)用CHECK短语指定列值应该满足的条件,An Introduction to Database System,26,5.3 用户定义的完整性,2.属性上约束条件检查和违约处理 当往表中插入元组或修改属性的值时，RDBMS就检查属性上的约束条件是否被满足，如果不满足操作被拒绝执行。,An Introduction to Database System,27,5.3 用户定义的完整性,3.元组上的约束条件的定义 在CREATE TABLE语句可以用CHECK短语定义元组上的约束条件，即元组级限制 元组级限制可以设置不同属性之间的取值的相互约束条件。,An Introduction to Database System,28,5.3 用户定义的完整性,3.元组上的约束条件的定义 在CREATE TABLE语句可以用CHECK短语定义元组上的约束条件，即元组级限制 元组级限制可以设置不同属性之间的取值的相互约束条件。,4.元组上的约束条件检查和违约处理,例：当学生的性别是男时，姓名不能以Ms.开头,An Introduction to Database System,29,第五章 数据库完整性,5.1 实体完整性 5.2 参照完整性 5.3 用户定义完整性 5.4 完整性约束命名子句 5.5 触发器,An Introduction to Database System,30,5.4 完整性约束命名子句,SQL在提供了完整性约束命名子句 CONSTRAINT，用来对完整性约束条件进行命名。 1.完整性约束命名子句 格式： CONSTRAINT PRIMARY KEY短语| FOREIGN KEY短语| CHECK短语 放置在任何一个约束短语的前面。,An Introduction to Database System,31,例10建立学生表student，姓名不能取空值，年龄小于30，性别只能取“男”或“女”。 CREATE TABLE Student ( Sno NUMERIC(6) Sname CHAR(8) NOT NULL, Ssex CHAR(2) CHECK(Ssex IN (男,女), Sage INT CHECK(Sage 30), PRIMARY KEY(Sno) );,CONSTRAINT C1 CONSTRAINT C2 CONSTRAINT C3 CONSTRAINT StuKey,An Introduction to Database System,32,5.4 完整性约束命名子句,2.修改表中的完整性限制 (1) 删除完整性限制 格式：DROP CONSTRAINT 定义约束时未命名，系统会自动命名。,例12 去掉例10 student表中对性别的限制。 ALTER TABLE Student DROP CONSTRAINT C3;,不能独立使用,(2) 修改完整性限制 先删除，再添加。,An Introduction to Database System,33,第五章 数据库完整性,5.1 实体完整性 5.2 参照完整性 5.3 用户定义完整性 5.4 完整性约束命名子句 5.5 触发器,An Introduction to Database System,34,5.5 触发器,1.触发器 (1)什么是触发器？ 触发器是一种特殊的存储过程 作用： 用来实现当使用update 、insert、delete语句对表进行操作时，自动触发的处理方法； 实现复杂的业务要求，类似于约束 保证数据的一致性。,An Introduction to Database System,35,5.5 触发器,1.触发器 (2)触发器的分类 After触发器：（默认类型） 触发Insert、Update、Delete动作之后执行。 （表中数据已经变动之后） Instead of触发器： 当执行Insert、Update、Delete操作时用其它操作来代替。 (操作不做，是完成触发器定义的操作),An Introduction to Database System,36,5.5 触发器,2.定义触发器 无论在“企业管理器”和“查询分析器”中，都用CREATE TRIGGER语句创建触发器。 格式：(P161) Create Trigger on for | after | instead of delete , insert , update as ,触发器的类型,触发事件,An Introduction to Database System,37,5.5 触发器,例1创建一个向学生表添加新记录时显示欢迎信息的触发器。 Create Trigger T_In On student after insert as print 欢迎新同学的到来！,An Introduction to Database System,38,5.5 触发器,3.触发器工作原理 (1)触发器工作时建立两个表： inserted表： 存储insert和update语句所影响的数据行副本 inserted中的行是触发器表中新行的副本。 deleted表： 存储delete和update语句所影响的数据行副本 deleted中的行是从触发器表中删除的行。 触发器表和deleted表没有相同的行,An Introduction to Database System,39,5.5 触发器,3.触发器工作原理 (2)这两个表的特点： 系统自动维护 对用户而言，是只读的 表结构与触发器作用的表结构相同 与触发器同时存在 （触发器运行结束，系统自动删除这两表）,An Introduction to Database System,40,5.5 触发器,3.触发器工作原理 (3)各种操作时触发器的工作： insert操作时触发器的工作 insert触发器将触发执行 新记录会添加到基本表和inserted表。 delete操作时触发器的工作 delete触发器将触发执行 被删除的记录会存放到deleted表中。 update操作时触发器的工作 相当于插入一条新纪录，删除一条旧记录。,An Introduction to Database System,41,5.5 触发器,例2创建一个限制年龄不能小于10的触发器 CREATE TRIGGER t1 on Student FOR Insert,Update AS IF( )10 BEGIN PRINT 年龄不能小于10，操作失败 ROLLBACK END;,select sage from inserted,An Introduction to Database System,42,5.5 触发器,4.建立触发器的条件 CREATE TRIGGER语句必须是批处理语句中的第一条； 只有表的拥有者可以创建触发器； 一个表只能创建一定数量的触发器； 在同一模式下，触发器名必须惟一； 当触发器被激活时，只有触发条件为真时触发动作体才执行。,An Introduction to Database System,43,5.5 触发器,例3为student表建立一个触发器t2，禁止删除学号为0215122的学生 create trigger t2 on student for update,delete as if (select sno from deleted)=0215122) begin print 不允许删除该学生，操作失败！ rollback end,An Introduction to Database System,44,5.5 触发器,例4为student表建立一个触发器t3，实现在更新中学号不可改变的要求。 CREATE TRIGGER t3 ON Student FOR Update AS IF Update(sno) BEGIN PRINT 学号不允许修改！ ROLLBACK END,An Introduction to Database System,45,5.5 触发器,例5建立触发器t4，要求插入的年龄值比表中记录的Sage都大。 create trigger t4 on student for update,insert as if (select sage from inserted)= (select max(sage) from student) begin print rollback end 例6建立触发器t5，要求修改的年龄比原来的Sage大,An Introduction to Database System,46,5.5 触发器,例6定义触发器，要求为教师表teacher插入或修改信息时教授的工资不能低于4000，如果低于4000，自动改为4000。,create trigger t6 on teacher for insert,update as if (select job from inserted)=教授) and (select sal from inserted)4000) begin update teacher set sal=4000 where eno=(select eno from inserted); end,An Introduction to Database System,47,5.5 触发器,5.激活触发器的过程 (1) 执行instead of触发器 (2) 执行SQL语句 (3) 执行af