Oracle基础知识第十二讲.ppt

第十二讲 数据完整性,二零一零年六月,目标,学习此章，需要掌握以下知识: 了解数据完整性的概念和作用 了解完整性约束的类型 了解完整性约束的工作机制 知道如何管理和维护数据的完整性 知道如何查看完整性约束的相关信息,概述,数据完整性是指数据库内的数据必须遵从的一套预定义规则。 此规则是由数据库管理员或应用程序开发者确定的。 Oracle 使用完整性约束防止用户向数据库的基表中插入无效数据。 完整性约束的作用是确保数据库内存储的信息遵从一定的业务规则。,完整性规则的类型,空规则：定义在某一列上的规则，其作用是允许或禁止将要被插入或更新的数据行此列的值为空值（null），即没有值 唯一列值：其作用是确保将要被插入或更新的数据行此列（或列集）的值是唯一的 主键值规则：其作用是确保表内的每一数据行都可以由某一个键值唯一地确定。,完整性规则的类型（续）,引用完整性规则：其作用是确保任意键值都能与相关表的某一键值（即引用值）相匹配。 复杂完整性检查 其作用是依据数据行的列值来允许或禁止插入，更新，或删除此数据行,如何强制实现数据完整性,使用完整性约束的好处,声明即用 规则集中化 应用程序开发效率最大化 即时向用户反馈 性能优势 数据加载时的灵活性 对违反完整性的数据的识别,使用完整性约束的影响,将带来一定的性能损失 一般来说，完整性约束造成的性能损失与进行约束检查所需执行的 SQL 语句大体相当,非空约束,默认情况下，表的所有列都允许为空值 空值的含义是未输入值。 约束要求表列内只能包含非空值。,UNIQUE key完整性约束要求列或列集的值唯一，数据表任意两行某列或某个列集的值不重复。 Oracle 使用索引来强制实现唯一完整性约束 。,唯一性约束,包含于 PRIMARY KEY 完整性约束定义内的列被称为主键。 表内的每行数据可以被唯一确定 表内不存在重复的数据行,主键约束,Oracle 使用索引来强制实现 PRIMARY KEY 约束。 例如：deptno 列上定义了主键约束，Oracle 的实现方式是隐式地创建索引 在此列上创建唯一索引（unique index） 在此列上定义 NOT NULL constraint（约束） 隐式创建的索引名称与主键约束名称相同。,主键约束（续）,在关系型数据库中，不同的表可以依据其共同的列产生关联关系，数据库需要确保数据遵从列关系的规则。引用完整性规则，就是用于确保列关系的规则。,引用完整性约束,引用完整性约束（续）,引用完整性约束的规则,限制:不允许对引用值进行更新与删除 置空:当引用值被更新或删除后，所有受影响的依赖值都将被赋予一个默认值。 置默认值 :当引用值被更新或删除后，所有受影响的依赖值都将被赋予一个默认值。 串联操作:当引用值被更新后，所有受影响的依赖值也将被更新为相同的值。当引用数据行（referenced row）被删除后，所有受影响的依赖数据行（dependent row）也将被删除。,引用完整性约束的规则（续）,要求数据行满足用户定义的检查条件 检查条件 布尔表达式 子查询,序列,SYSDATE，UID，USER, USERENV 等 SQL 函数 LEVEL 或 ROWNUM 虚列 多重 CHECK 约束 多个，不受限制 不冲突,check完整性约束,约束条件的状态,DISABLE NOVALIDATE：不检查新数据和现有数据，因此这些数据可能不符合约束条件。 DISABLE VALIDATE：如果约束条件处于此状态，则不允许对有约束条件的列进行任何修改。 ENABLE NOVALIDATE：新数据符合约束条件，但现有数据处于未知状态。 ENABLE VALIDATE：新数据与现有数据均符合约束条件。这是约束条件的典型状态和默认状态。,约束条件的状态（续）,约束条件检查的设置,可延迟的（deferrable）与不可延迟的（not deferrable） 可延迟的，指的是提交事务处理时才检查约束条件。如果在提交时检测到任何违反约束条件的情况，则会回退整个事务处理 不可延迟的，又称为即时约束，是在每个DML语句结束时强制执行的。违反约束条件会导致语句的回滚 定义为“非延迟”的约束条件不能更改为延迟的约束条件,约束条件检查的设置（续）,延迟开始（initially deferred）与立即开始（initially immediate） 延迟开始，指的是在默认情况下，只在事务处理结束时强制使用的约束条件 立即开始，指的是在默认情况下，约束条件必须用作即时约束，除非另外显示进行了设置 使用set constraints 语句可以对约束条件的检查进行设置,约束条件的检查,exceptions表,表EXCEPTIONS 记录着任何违反已启用约束的行的信息 使用EXCEPTIONS表检测违反约束的数据 ： a. 如果尚未创建EXCEPTIONS，则通过运行utlexcpt.sql 脚本来创建例外表。 SQL ?/rdbms/admin/utlexcpt.sql b. 执行带有EXCEPTIONS 选项的ALTER TABLE 语句。 ALTER TABLE table_name ENABLE VALIDATE CONSTRAINT const_name EXCEPTIONS INTO system.exceptions;,Exceptions表（续）,使用EXCEPTIONS表检测违反约束的数据 ： c. 使用EXCEPTIONS 上的子查询定位包含无效数据的行 SELECT rowid, FROM table_name WHERE ROWID in (SELECT row_id FROM system.exceptions) FOR UPDATE; d. 纠正错误。 UPDATE table_name SET WHERE rowid=. e. 截断EXCEPTIONS 表，并重新执行ALTER TABLE 以启用约束。 TRUNCATE TABLE system.exceptions; ALTER TABLE table_name ENABLE VALIDATE CONSTRAINT const_name EXCEPTIONS INT