第8章 数据的完整性.ppt

1、第8章 数据库的完整性 P151,数据库的完整性是指数据的正确性和相容性。 例如，学生的学号必须唯一；性别只能是男或女；等。 数据库是否具备完整性关系到数据库系统能否真实地反映现实世界，因此维护数据库的完整性是非常重要的。,为了维护数据库的完整性，DBMS必须提供如下功能： 1.定义功能：提供定义完整性约束条件的机制。 2.检查功能：检查用户发出的操作请求是否违背了完整性约束条件。 完整性检查的时机： （1）立即执行约束：在一条语句执行完后立即检查是否违背完整性约束。 （2）延迟执行约束：有时完整性检查需要延迟到整个事务执行结束后再进行，检查正确方可提交，称这类约束为延迟执行约束。 3. 违约

2、处理：如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的操作，以保证数据的完整性。,8.1 实体完整性的定义 P152 实体完整性的检查及违约处理 P153 8.2 参照完整性的定义 P154 参照完整性的检查及违约处理 P154,实现参照完整性要考虑的几个问题 P155 表5.1,1在被参照关系中删除元组的问题 当删除被参照关系的某个元组，而参照关系存在若干元组，其外码值与被参照关系删除元组的主码值相同，这时可有三种不同的策略： (1)级联删除(CASCADES) 将参照关系中所有外码值与被参照关系中要删除元组主码值相同的元组一起删除。 (2)受限删除 仅当参照关系中没有任何元组

3、的外码值与被参照关系中要删除元组的主码值相同时，系统才执行删除操作，否则拒绝此删除操作。 (3)置空值删除 删除被参照关系的元组，并将参照关系中相应元组的外码值置空值。,2在参照关系中插入元组时的问题 当参照关系插入某个元组，而被参照关系不存在相应的元组，其主码值与参照关系插入元组的外码值相同，这时可有以下策略： (1)受限插入 仅当被参照关系中存在相应的元组，其主码值与参照关系插入元组的外码值相同时，系统才执行插入操作，否则拒绝此操作。 (2)递归插入 首先向被参照关系中插入相应的元组，其主码值等于参照关系插入元组的外码值，然后向参照关系插入元组。,3修改被参照表主码值的问题 (1)不允许修

4、改主码值 在有些RDBMS中，不允许修改被参照表的主码值。 (2)允许级联修改或外码值置空 在有些RDBMS中，允许修改关系主码,但必须保证主码值与对应外码值一致。,4外码是否允许空值的问题 实现参照完整性时，系统除了应提供定义外码的机制，还应提供定义外码列是否允许空值的机制。 例：部门表与职工部门表，职工部门表中部门可取空值；但学生表与学生选课表，学生选课表不能取空值。,8.3 用户定义的完整性 P156P159 8.4 完整性约束命名子句 P158,5. 5 用触发器实现数据的完整性,1. 触发器简介 触发器是定义在表或视图上的一类特殊的子程序，用于维护表中的数据，当有操作影响到触发器关联

5、的数据时，触发器自动执行，例如：通过触发器维护多个表间数据的一致性。 一般情况下，对表数据的操作有：插入、修改、删除，因而维护数据的触发器也可分为三种类型：INSERT、UPDATE和DELETE触发器。 同一个表可定义多个触发器。,2. Oracle触发器的定义 P161-163,3. SQL SERVER触发器的定义 利用触发器可以方便地实现数据库中数据的完整性。 例如，对于图书管理数据库xsbook如下： xs(借书证号,姓名,专业名，性别,出生时间,借书数,照片) Book(ISBN,书名,作者,出版社,价格,复本数,库存数) JY(借书证号,ISBN,索书号,借书日期),SQL Se

6、rver 触发器定义的语法,CREATE TRIGGER trigger_name ON table | view /*指定触发器名及操作对象*/ FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE /*定义触发器的类型*/ AS sql_statements /* T- SQL语句序列*/,触发器中使用的特殊表,执行触发器时，系统创建了两个特殊的逻辑表:inserted表和deleted表， inserted 逻辑表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。 deleted逻辑表：用于保存已

7、从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表中。,修改一条记录等于插入一新记录，同时删除旧记录。当对定义了UPDATE触发器的表记录修改时，表中原记录移到deleted表中，修改过的记录插入到inserted表中。 触发器可检查deleted表、inserted表及被修改的表。 例如，若要检索 deleted、inserted 表中的所有记录，可使用如下语句： SELECT * FROM deleted SELECT * FROM inserted deleted、inserted 逻辑表的查询方法与数据库用户表的查询方法相同。,使用触发器的限制,(

8、1) CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。 (2) 触发器只能在当前的数据库中创建。 (3) 在同一CREATE TRIGGER语句中，可以为多种操作（如 INSERT 和 UPDATE）定义相同的触发器操作。,(4) 如果一个表的外键在 DELETE、UPDATE 操作上定义了级联，则不能在该表上定义 INSTEAD OF DELETE、INSTEAD OF UPDATE 触发器。 (5) 在触发器内可以指定任意的 SET 语句，所选择的 SET 选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。 (6) 触发器中不允许包含以下 T-

9、SQL 语句： CREATE DATABASE 、ALTER DATABASE 、LOAD DATABASE 、RESTORE DATABASE 、DROP DATABASE、LOAD LOG 、RESTORE LOG 、DISK INIT、DISK RESIZE和RECONFIGURE (7) 触发器不能返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含 SELECT 语句或给变量赋值。,【例】在数据库XSBOOK中创建一触发器，当向JY表插入一记录时，检查该记录的借书证号在XS表是否存在，检查图书的ISBN号在BOOK表中是否存在，及图书的库存量是否大于0，若有一项为否，则不允

10、许插入，在满足条件的情况下进行插入。,USE XSBOOK IF EXISTS(SELECT name FROM sysobjects WHERE NAME=tjy_insert and type=TR) DROP TRIGGER tjy_insert GO CREATE TRIGGER tjy_insert ON jy FOR INSERT AS IF EXISTS(SELECT * FROM inserted a WHERE a.借书证号 NOT IN (SELECT b.借书证号 FROM XS b) OR a.ISBN NOT IN (SELECT c.ISBN FROM BOOK c

11、) OR EXISTS(SELECT * FROM BOOK WHERE 库存量=0 AND a.ISBN= BOOK.ISBN) BEGIN RAISERROR (违背数据的一致性., 16, 1) ROLLBACK TRANSACTION END,ELSE BEGIN UPDATE XS SET借书数=借书数+1 WHERE XS.借书证号 IN (SELECT inserted.借书证号 FROM inserted) UPDATE BOOK SET 库存量=库存量-1 WHERE book.ISBN IN (SELECT inserted.ISBN FROM inserted) END

12、GO,【例】在XSBOOK数据库的JY表上创建一触发器，若对借书证号列和图书的ISBN列修改，则给出提示信息，并取消修改操作，用两种方法实现。,方法1：利用IF UPDATE(column)子句实现： USE XSBOOK GO CREATE TRIGGER update_trigger1 ON JY FOR UPDATE AS /*检查借书证号列或ISBN列是否被修改，如果有某些列被修改了，则取消修改操作*/ IF UPDATE(借书证号) or UPDATE(ISBN) BEGIN RAISERROR (违背数据的一致性., 16, 1) ROLLBACK TRANSACTION END

13、GO,方法2：调用 COLUMNS_UPDATED()函数，检查借书证号列或ISBN列是否被修改。 USE XSBOOK GO CREATE TRIGGER update_trigger2 ON JY FOR update AS /*检查借书证号列(C0)和ISBN列(C1)是否被修改，如果有某些列被修改了，则取消修改操作*/ IF (COLUMNS_UPDATED() & 3) 0 BEGIN RAISERROR (违背数据的一致性., 16, 1) ROLLBACK TRANSACTION END GO,JY表INSERT触发器的定义 USE XSBOOK GO CREATE TRIGGE

14、R tjy_insert ON dbo.JY FOR INSERT AS BEGIN UPDATE XS SET借书数=借书数+1 WHERE XS.借书证号 IN (SELECT inserted.借书证号 FROM inserted) UPDATE BOOK SET库存量=库存量-1 WHERE book.ISBN IN (SELECT inserted.ISBN FROM inserted) END,JY表DELETE触发器的定义 CREATE TRIGGER tjy_delete ON jy FOR DELETE AS BEGIN declare ssh char(10),jszh c

15、har(8),isbn char(16) declare jssj datetime,hssj datetime UPDATE XS SET借书数=借书数-1 WHERE XS.借书证号 IN (SELECT deleted.借书证号 FROM deleted) UPDATE BOOK SET库存量=库存量+1 WHERE BOOK.ISBN IN (SELECT deleted.ISBN FROM deleted) SELECT ssh=索书号,jszh=借书证号,isbn=ISBN,jssj=借书时间 FROM deleted INSERT JYLS(索书号,借书证号,ISBN,借书时间,还书时间) VALUES(ssh,jszh,isbn,jssj,getdate() END,CREATE TRIGGER trigger_name ON table | view /*指定触发器名及操作对象*/ WITH ENCRYPTION /*说明是否采用加密方式*/ FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE /*定义触发器的类型