Oracle维护数据完整性

1、13Copyright Oracle Corporation, 2001. All rights reserved.13Copyright Oracle Corporation, 2001. All rights reserved.维护数据完整性维护数据完整性 13-3Copyright Oracle Corporation, 2001. All rights reserved.目标目标 完成这一课的学习后，您应该能达到下列目标：完成这一课的学习后，您应该能达到下列目标： 实施数据完整性约束实施数据完整性约束 维护完整性约束维护完整性约束 从数据字典获取约束信息从数据字典获取约束信息 13-4

2、Copyright Oracle Corporation, 2001. All rights reserved.数据完整性数据完整性 应用程序代码应用程序代码 表表 数据数据 完整性约束完整性约束 数据库触发器数据库触发器 13-5Copyright Oracle Corporation, 2001. All rights reserved.13-6Copyright Oracle Corporation, 2001. All rights reserved.约束的类型约束的类型 约束约束NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK说明说明 指示出列不能包

3、含空值指示出列不能包含空值 指示一个列或列的组合是唯一的指示一个列或列的组合是唯一的 指示一个列或列的组合作为表的主键指示一个列或列的组合作为表的主键 指示一个列或列的组合在引用完整性指示一个列或列的组合在引用完整性约束中作为外键约束中作为外键 指定表中的每一行必须满足的条件指定表中的每一行必须满足的条件 13-7Copyright Oracle Corporation, 2001. All rights reserved.约束的状态约束的状态 ENABLENOVALIDATE现有数据现有数据 新数据新数据 DISABLENOVALIDATEDISABLEVALIDATE=ENABLEVALI

4、DATE13-8Copyright Oracle Corporation, 2001. All rights reserved.约束的状态转换规则约束的状态转换规则 除非指定除非指定 NOVALIDATE，否则否则 ENABLE 表示表示 VALIDATE。 除非指定除非指定 VALIDATE，否则否则 DISABLE 表示表示 NOVALIDATE。 VALIDATE 和和 NOVALIDATE 没有缺省的没有缺省的 ENABLE 和和 DISABLE 状态。状态。 当唯一键或主键从当唯一键或主键从 DISABLE 状态转为状态转为 ENABLE 状态且没有现有索引时，状态且没有现有索引时，

5、将自动创建唯一索引。（如果索引可延迟，则将存在异常。）与此类似，将自动创建唯一索引。（如果索引可延迟，则将存在异常。）与此类似，当唯一键或主键从当唯一键或主键从 ENABLE 转为转为 DISABLE 且是使用唯一索引启用时，且是使用唯一索引启用时，则删除该唯一索引。则删除该唯一索引。 当任何约束从当任何约束从 NOVALIDATE 状态转为状态转为 VALIDATE 状态时，必须检查所状态时，必须检查所有的数据。但是，从有的数据。但是，从 VALIDATE 转为转为 NOVALIDATE 时，将忽略数据已时，将忽略数据已经过检查这一事实。经过检查这一事实。 将单个约束从将单个约束从 ENAB

6、LE NOVALIDATE 状态转为状态转为 ENABLE VALIDATE 状态时，并不禁止使用读取、写入或其它状态时，并不禁止使用读取、写入或其它 DDL 语句。语句。 13-9Copyright Oracle Corporation, 2001. All rights reserved.约束检查约束检查 DML 语句语句 检查不可延迟检查不可延迟的约束的约束 COMMIT检查可延迟检查可延迟的约束的约束 13-10Copyright Oracle Corporation, 2001. All rights reserved.查看约束信息查看约束信息 SELECT CONSTRAINT_N

7、AME,CONSTRAINT_TYPE,TABLE_NAME,STATUS,DEFERRABLE, DEFERRED,VALIDATED FROM USER_CONSTRAINTS;13-11Copyright Oracle Corporation, 2001. All rights reserved.将约束定义为立即或延迟将约束定义为立即或延迟 使用使用 SET CONSTRAINTS ：SET CONSTRAINT | CONSTRAINTS constraint |ALL IMMEDIATE|DEFERRED ALTER SESSION 语句还包含将约束设置为语句还包含将约束设置为 DE

8、FERRED 或或 IMMEDIATE 的子句的子句 SET CONSTRAINTS。ALTER SESSIONSET CONSTRAINTS =IMMEDIATE|DEFERRED|DEFAULT13-12Copyright Oracle Corporation, 2001. All rights reserved.约束定义原则约束定义原则 主约束和唯一性约束：主约束和唯一性约束： 将索引放在单独的表空间中。将索引放在单独的表空间中。 如果经常使用批量加载，请使用非唯一索引。如果经常使用批量加载，请使用非唯一索引。 自引用外键：自引用外键： 在初始加载后定义或启用外键。在初始加载后定义或启用

9、外键。 延迟约束检查。延迟约束检查。 13-13Copyright Oracle Corporation, 2001. All rights reserved.外键注意事项外键注意事项 相应解决方法相应解决方法 目标操作目标操作 删除父表删除父表 级联约束级联约束 截断父表截断父表在子表上执行在子表上执行 DML 操作操作确保包含父键的表空间联机确保包含父键的表空间联机 使用使用 CASCADE CONSTRAINTS 子句子句删除包含父表的表空间删除包含父表的表空间 禁用或删除外键禁用或删除外键13-14Copyright Oracle Corporation, 2001. All righ

10、ts reserved.约束的定义约束的定义在创建表的时候定义在创建表的时候定义在修改表的时候定义在修改表的时候定义13-15Copyright Oracle Corporation, 2001. All rights reserved.约束的结构约束的结构 column datatype CONSTRAINT constraintNOT NULL|UNIQUE USING INDEX index_clause|PRIMARY KEY USING INDEX index_clause|REFERENCES schema.table (column) ON DELETE CASCADE|CHEC

11、K (condition)constraint_state :=NOT DEFERRABLE|DEFERRABLE INITIALLY IMMEDIATE|DEFERREDDISABLE|ENABLE VALIDATE|NOVALIDATE13-16Copyright Oracle Corporation, 2001. All rights reserved.创建表时定义约束创建表时定义约束create table create_table_cons (id1 char(10) constraint cts_id_pk primary keyDeferrableInitially deferr

12、edusing indextablespace ind1_00001,id2 varchar2(20)tablespace users;13-17Copyright Oracle Corporation, 2001. All rights reserved.修改表时定义约束修改表时定义约束 alter table create_table_cons add constraint cts_id2_uk unique(id2) deferrable initially immediate using index tablespace ind1_00001;13-18Copyright Oracle

13、 Corporation, 2001. All rights reserved.13-19Copyright Oracle Corporation, 2001. All rights reserved.启用约束启用约束 对于当前已有索引的对于当前已有索引的 PRIMARY KEY 和和 UNIQUE 约束，启用约束，启用 NOVALIDATE 约束比约束比启用启用 VALIDATE 约束要快得多，这是因为，约束要快得多，这是因为，如果约束是可延迟的，则不检查现有数据如果约束是可延迟的，则不检查现有数据是否违反约束。是否违反约束。不要求锁定表。不要求锁定表。ENABLE NOVALIDATE a

14、lter table create_table_consenable novalidate constraint cts_id_pk;13-20Copyright Oracle Corporation, 2001. All rights reserved.启用启用 ENABLE NOVALIDATE 约束约束:ALTER TABLE schema. tableENABLE NOVALIDATE CONSTRAINT constraint| PRIMARY KEY| UNIQUE ( column , column . ) USING INDEX index_clause 限制：限制： USIN

15、G INDEX 子句仅适用于创建为可延迟的主键约束或唯一性约束，并且下列条件之一为真的情况： 约束被创建为禁用。 约束被禁用且索引已删除。13-21Copyright Oracle Corporation, 2001. All rights reserved.13-22Copyright Oracle Corporation, 2001. All rights reserved.启用约束启用约束 锁定表锁定表 需要有效的表数据需要有效的表数据 如果索引列上不存在索引，如果索引列上不存在索引，Oracle 服务器服务器就会创建一个索引。当启用不可延迟的主就会创建一个索引。当启用不可延迟的主键约束

16、或唯一性约束时，键约束或唯一性约束时，Oracle 服务器将服务器将创建一个唯一索引。对于可延迟的主键约创建一个唯一索引。对于可延迟的主键约束或唯一性约束，将建立一个非唯一索引。束或唯一性约束，将建立一个非唯一索引。 ENABLE VALIDATE alter table create_table_cons enable validate constraint cts_id2_uk;13-23Copyright Oracle Corporation, 2001. All rights reserved.启用启用 ENABLE VALIDATE 约束约束: ALTER TABLE schema.

17、 table ENABLE VALIDATE CONSTRAINT constraint| PRIMARY KEY| UNIQUE ( column , column . ) USING INDEX index_clause EXCEPTIONS INTO schema. table 13-24Copyright Oracle Corporation, 2001. All rights reserved.查找并消除违反约束的行查找并消除违反约束的行 通过运行通过运行 utlexpt1.sql （ utlexcpt.sql）脚本来创建）脚本来创建 EXCEPTIONS表。表。 执行带有执行带有

18、EXCEPTIONS 选项的选项的 ALTER TABLE 语句。语句。 使用使用 EXCEPTIONS 上的子查询定位包含无效数据的行。上的子查询定位包含无效数据的行。 纠正错误。纠正错误。 重新执行重新执行 ALTER TABLE 以启用约束。以启用约束。13-25Copyright Oracle Corporation, 2001. All rights reserved.查找并消除违反约束的行查找并消除违反约束的行1. D:oracleproduct10.2.0db_1RDBMSADMINutlexpt1.sql2. alter table student enable validat

19、e constraint PK_STUDENT exceptions into exceptions3. select rowid , sno, sname from student where rowid in (select row_id from exceptions) for update;4. update student set sno=200215127 where rowid=AAACYsAABAAAJ0zAAE;5. alter table student enable validate constraint pk_student;13-26Copyright Oracle Corporation, 2001. All rights reserved.13-27Copyright Oracle Corporation, 2001. All rights reserved.13-28Copyright Oracle Corporation, 2001. All rights reserved.获取约束信息获取约束信息 通过查询