维护数据完整性.ppt

O Oracleracle体系结构与管理体系结构与管理第十三章第十三章 维护数据完整性维护数据完整性 本章要点本章要点è数据完整性概述数据完整性概述 è完整性约束完整性约束 è实现约束实现约束 è维护约束维护约束 è查询约束信息查询约束信息 本章教学目标本章教学目标l理解数据完整性的基本概念及数据完整性规则；l理解Oracle数据库的完整性约束的条件、状态和检查时间等概念；l掌握定义列级约束和表级约束的技术和方法；l掌握添加、修改和删除约束的技术和方法13.1 数据完整性概述数据完整性概述l数据完整性数据完整性 数据完整性是指存储在数据库中数据的一致性和正确性根据数据完整性作用的数据库对象和范围不同，可以将数据完整性分为:Ø实体完整性(Entity Integrity)Ø域完整性(Domain Integrity)Ø引用完整性(Reference Integrity)Ø用户定义完整性(User Defined Integrity)13.1 数据完整性概述数据完整性概述l数据完整性数据完整性 作用：Ø实体完整性可以保证表内每条记录的唯一性 Ø域完整性可以保证表内数据项的合理性和有效性。

Ø引用完整性又称参照完整性，可以保证引用表和被引用表之间的数据一致性 Ø用户定义完整性是指用户可以定义不属于其他任何完整性分类的特定业务规则 13.1 数据完整性概述数据完整性概述l数据完整性规则数据完整性规则 Oracle应用于关系数据库数据完整性有下列4种类型的规则：ØNULL规则 在插入或修改表的行时是否允许包含有NULL的值Ø唯一性规则 保证插入或修改的记录在字段值上的唯一性Ø引用完整性规则 保证多个相关表的一致性Ø用户自定义规则 可实现复杂的完整性检查13.1 数据完整性概述数据完整性概述l数据完整性规则数据完整性规则数据完整性的实现方法：Ø应用程序代码Ø完整性约束 Ø数据库触发器13.1 数据完整性概述数据完整性概述l数据完整性规则数据完整性规则利用完整性约束实施数据完整性规则有下列优点：Ø创建或修改表时直接通过SQL语句定义完整性约束，无需额外的编程，可减少程序性错误Ø完整性约束存储在数据字典中，任何进入表中的数据都必须接受完整性约束，可以保证数据库中所有数据的完整性Ø使用完整性约束可以分离数据和程序ØDBA可以通过约束管理语句灵活地设置完整性约束的有效性。

Ø由于完整性约束存储在数据字典中，DBA、开发人员和应用程序可以快速查询事务规则，选择正确的操作或数据13.2 完整性约束完整性约束l约束条件约束条件 Ø非空约束（NOT NULL）Ø唯一性约束（UNIQUE）Ø主键约束（PRIMARY KEY）Ø外键约束（FOREIGN KEY）Ø检查约束（CHECK）13.2 完整性约束完整性约束l约束条件约束条件 通过EMPLOYEE表的创建说明各种约束条件的作用CREATE TABLE EMPLOYEE （EMPNO NUMBER（10） PRIMARY KEY， NAME VARCHAR2（40） NOT NULL， SEX CHAR（1）， DEPTNO NUMBER（2） DEFAULT 10， SALARY NUMBER（7，2） CHECK（SALARY<1000000.00）， SOCNUM CHAR(14) UNIQUE， FOREIGN KEY （DEPTNO） REFERENCES DEPT（DEPTNO）） TABLESPACE USERS；13.2 完整性约束完整性约束l约束状态约束状态 1.激活和禁用Ø激活状态（ENABLE） 在这种状态下，完整性约束被激活，Oracle将对输入到表中的数据进行完整性约束检查，如果数据不符合约束，则不允许保存到表中，所执行的操作被回退。

一般情况下，完整性约束总是处于被激活状态以保证数据完整性Ø禁用状态（DISABLE） 在这种状态下，完整性约束被禁止，Oracle不对输入到表中的数据进行完整性约束检查，任何数据都可以存入表中13.2 完整性约束完整性约束l约束状态约束状态 2.验证和非验证Ø验证状态（VALIDATE） 在这种状态下，在定义或激活约束时，Oracle检查表中该列中的数据是否满足约束条件Ø非验证状态（NOVALIDATE） 在这种状态下，在定义或激活约束时，Oracle不检查表中该列中的数据是否满足约束条件13.2 完整性约束完整性约束l约束状态约束状态 根据以上情况可以将完整性约束组合成4种状态 ：ØENABLE VALIDATE 表中的数据必须满足约束条件，将要输入的数据也要满足约束条件 ØENABLE NOVALIDATE Oracle不对表中的数据进行约束验证，只对将要输入的数据进行约束检查 ØDISABLE VALIDATE 约束被禁用，所有将要输入的数据均不作约束检查，并且不允许作任何插入、删除或修改操作 ØDISABLE NOVALIDATE 约束禁用，并且不对表中的数据进行约束验证。

13.2 完整性约束完整性约束l约束检查的时间约束检查的时间 通常情况下，Oracle在一条DML语句执行结束后立即进行完整性约束检查，如果发现数据不满足约束条件，则回退该操作但是，有时不希望执行完一条DML语句后立即检查约束，而是多条DML语句执行完后再进行检查，这时可以在事务提交（COMMIT命令）时统一检查约束，如果任何一个数据不满足约束条件，Oracle将回退整个事务 13.2 完整性约束完整性约束l约束检查的时间约束检查的时间 1.非延迟约束 非延迟约束也叫立即约束，是在一条DML语句执行完后立即进行完整性约束检查，如果发现数据不满足约束条件，则回退该操作非延迟约束一经定义，不允许将检查时间修改在提交事务的时候，除非删除该约束，重新定义约束为延迟约束 非延迟约束是约束的默认值，如果要显式定义非延迟约束可以在定义约束时使用NO DEFERRABLE子句13.2 完整性约束完整性约束l约束检查的时间约束检查的时间 2.可延迟约束 可延迟约束是指可以指定约束检查的时间，可以在DML语句执行完后不立即进行约束检查，而是延迟到事务提交命令COMMIT执行时检查。

可延迟约束是在定义约束时指定了DEFERRABLE子句该子句有两种检验模式：ØINITIALLY IMMEDIATE 立即检验模式这种模式同非延迟约束一样，是在DML语句执行完后立即检查约束这是可延迟约束的缺省值ØINITIALLY DEFERRED 延迟检验模式这种模式不在DML语句执行完后立即检查约束，而是推迟到事务提交命令COMMIT执行完后13.2 完整性约束完整性约束l约束检查的时间约束检查的时间 2.可延迟约束 可延迟约束一经定义，不允许改变为非延迟约束，除非删除该约束，重新定义约束为非延迟约束但是通过改变可延迟的检验模式可以达到非延迟约束的效果设置验证模式可以执行SET CONSTRAINT命令实现，如：SQL>SET CONSTRAINT ALL IMMEDIATE；该命令将所有可延迟约束设置为立即检验模式13.2 完整性约束完整性约束l约束检查的时间约束检查的时间 2.可延迟约束设置当前会话级的检验模式，可以执行下列命令：ALTER SESSION SET CONSTRAINT ={IMMEDIATE|DEFERRED|DEFAULT}；DEFAULT表示保持原来的检验模式。

13.3 实现约束实现约束l 定义列级约束定义列级约束 【任务13.1】创建STUDENTS表时，对表中各字段设置完整性约束，如ID字段定义为该表的主键，并且设置为可延迟约束，约束为激活状态，NAME、SEX、COURSE和SCORE字段定义NOT NULL约束13.3 实现约束实现约束l 定义列级约束定义列级约束 作用于单个列上的约束称之为列级约束列级约束是在定义列的子句中设置列级约束定义子句的语法为：column datatype [CONSTRAINT constraint] {[NOT] NULL |UNIQUE [USING INDEX index_clause] |PRIMARY KEY [USING INDEX index_clause] |REFERENCES [schema.]table [(column)] [ON DELETE CASCADE] |CHECK (condition)} [NOT DEFERRABLE|DEFERRABLE [INITIALLY {IMMEDIATE|DEFERRED}]] [DISABLE|ENABLE [VALIDATE|NOVALIDATE]]13.3 实现约束实现约束l 定义列级约束定义列级约束各关键字和参数的含义如下： Øconstraint 定义约束名。

如果缺省，Oracle自动命名ØNOT NULL 定义NOT NULL约束如果为NOT NULL，表示该列不允许有空值；如果为NULL，表示该列可以有空值ØUNIQUE 定义唯一性约束，并可以通过index_clause子句定义索引ØPRIMARY KEY 定义主键约束，并可以通过index_clause子句定义索引ØREFERENCES 定义外键约束，并指出被引用表的表名和列ØON DELETE CASCADE 表示当删除父表的数据时，将子表中与父表被删除数据相关的数据一起删除ØCHECK 定义检查约束13.3 实现约束实现约束l 定义列级约束定义列级约束各关键字和参数的含义如下：ØNOT DEFERRABLE 定义非延迟约束该值是缺省值ØDEFERRABLE 定义可延迟约束ØINITIALLY IMMEDIATE 设置约束检查为立即检验模式该值是缺省值ØINITIALLY DEFERRED 设置约束检查为延迟检验模式ØENABLE 设置约束为激活状态该值是缺省值ØDISABLE 设置约束为禁用状态ØVALIDATE 设置约束为验证状态该值是缺省值ØNOVALIDATE 设置约束为非验证状态。

13.3 实现约束实现约束l 定义列级约束定义列级约束SQL>CREATE TABLE TEST.STUDENTS 2 (ID VARCHAR2(10) 3 CONSTRAINT STUDENTS_ID_PK PRIMARY KEY4 DEFERRABLE 5 USING INDEX6 STORAGE(INITIAL 100K NEXT 100K)7 TABLESPACE INDEXS8 ENABLE,9 NAME VARCHAR2(10) NOT NULL, 10 SEX VARCHAR2(2) NOT NULL, 11 COURSE VARCHAR2(20) NOT NULL, 12 SCORE NUMBER(3)) 13 TABLESPACE USER01 14 PCTFREE 20 15 PCTUSED 40 16 INITRANS 1 17 MAXTRANS 100 18 STORAGE ( 19 INITIAL 400K 20 NEXT 400K 21 MINEXTENTS 2 22 MAXEXTENTS 20023 PCTINCREASE 20 24 BUFFER_POOL RECYCLE)；13.3 实现约束实现约束l定义表级约束定义表级约束 【任务13.2】创建EMPLOYEE表时，对表中FIRST_NAME和LAST_NAME两个字段组合在一起设置成唯一性约束，该约束为表级约束。

13.3 实现约束实现约束l定义表级约束定义表级约束 表级约束子句的语法为： [CONSTRAINT constraint] {PRIMARY KEY (column [, column ]... ) [USING INDEX index_clause] |UNIQUE (column [, column ]... ) [USING INDEX index_clause] |FOREIGN KEY (column [, column ]... ) REFERENCES [schema.]table [(column [, column ]... )] [ON DELETE CASCADE] |CHECK (condition)} [NOT DEFERRABLE|DEFERRABLE [INITIALLY {IMMEDIATE|DEFERRED}]] [DISABLE|ENABLE [VALIDATE|NOVALIDATE]] 除了不能在表级约束子句中不能定义NOT NULL约束外，该子句所有关键字和参数的含义同列级约束定义子句是一样的13.3 实现约束实现约束l定义表级约束定义表级约束 SQL>CREATE TABLE TEST.EMPLOYEE2 （EMPNO NUMBER（10） PRIMARY KEY，3 FIRST_NAME VARCHAR2（40） NOT NULL，4 LAST_NAME VARCHAR2（40） NOT NULL，5 SEX CHAR（1），6 DEPTNO NUMBER（2）7 SALARY NUMBER（7，2） CHECK（SALARY<1000000.00），8 SOCNUM CHAR(14) UNIQUE，9 CONSTRAINT NAME UNIQUE(FIRST_NAME，LAST_NAME)10 DEFERRABLE 11 DISABLE VALIDATE）12 TABLESPACE USERS；13.4 维护约束维护约束l维护约束命令维护约束命令维护约束语句的语法为：ALTER TABLE [ schema. ]tableADD out_of_line_constraint [ out_of_line_constraint ]...MODIFY { CONSTRAINT constraint | PRIMARY KEY | UNIQUE (column) [, UNIQUE (column) ]... [DEFERRABLE [INITIALLY {IMMEDIATE|DEFERRED}]] [DISABLE|ENABLE [VALIDATE|NOVALIDATE]]RENAME CONSTRAINT old_name TO new_namedrop_constraint_clause；13.4 维护约束维护约束l维护约束命令维护约束命令维护约束命令包含了4个子句，其功能为：ØADD 添加新约束子句。

ADD后面跟有表级约束定义子句，添加新的约束只能是表级约束ØMODIFY 修改约束状态子句可以将现有约束的状态改为ENABLE VALIDATE、ENABLE NOVALIDATE、DISABLE VALIDATE和DISABLE NOVALIDATEØRENAME 重新命名约束名子句Ødrop_constraint_clause 删除已有约束子句13.4 维护约束维护约束l添加约束添加约束 【任务13.3】 在STUDENTS表中添加一个检查约束，使学生成绩不能小于0分，也不能大于100分为表TEST.STUDENTS的SCORE定义一个检查约束SQL> ALTER TABLE TEST.STUDENTS 2 ADD (CONSTRAINT SCORE_CHECK 3 CHECK(score>=0 and score<=100))；结果显示为：表已更改13.4 维护约束维护约束l修改约束状态修改约束状态 【任务13.4】 对STUDENTS表中SCORE_CHECK约束的状态进行设置，使之禁用、激活、验证或非验证在创建该约束时将其设置为禁用状态：SQL>ALTER TABLE TEST.STUDENTS 2 ADD (CONSTRAINT SCORE_CHECK3 CHECK(score>=0 and score<=100) DISABLE)；结果显示为：表已更改。

13.4 维护约束维护约束l修改约束状态修改约束状态激活该约束：SQL>ALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 ENABLE；结果显示为：表已更改13.4 维护约束维护约束l修改约束状态修改约束状态修改约束状态为验证状态：SQL>ALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 VALIDATE；结果显示为：表已更改13.4 维护约束维护约束l修改约束状态修改约束状态修改约束状态为非验证状态：SQL>ALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 NOVALIDATE；结果显示为：表已更改13.4 维护约束维护约束l修改约束状态修改约束状态设置约束状态为ENABLE VALIDATE：SQL>ALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 ENABLE VALIDATE；结果显示为：表已更改。

13.4 维护约束维护约束l修改约束状态修改约束状态 利用ALTER TABLE语句也可以修改约束的状态，其语法为：ALTER TABLE [ schema. ] table | ENABLE | DISABLE | NOVALIDATE | NOVALIDATE {CONSTRAINT constraint | PRIMARY KEY | UNIQUE ( column [, column ] ... ) } [ USING INDEX index_clause ；13.4 维护约束维护约束l修改约束状态修改约束状态激活SCORE_CHECK约束，并设置该约束为验证状态：SQL>ALTER TABLE TEST.STUDENTS 2 ENABLE VALIDATE CONSTRAINT SCORE_CHECK；要禁用SCORE_CHECK约束，并设置该约束为非验证状态：SQL>ALTER TABLE TEST.STUDENTS 2 DISABLE NOVALIDATE CONSTRAINT 3 SCORE_CHECK； 13.4 维护约束维护约束l修改约束延迟修改约束延迟 【任务13.5】 为表STUDENTS的SEX字段定义一个可延迟约束，使之只能为1或0，并设置其为立即检验模式或延迟检验模式。

13.4 维护约束维护约束l修改约束延迟修改约束延迟为表TEST.STUDENTS的SEX定义一个非延迟约束SQL>ALTER TABLE TEST.STUDENTS 2 ADD （CONSTRAINT SEX_CHECK 3 CHECK(SEX=0 or SEX=1) 4 NOT DEFERRABLE）；结果显示为：表已更改13.4 维护约束维护约束l修改约束延迟修改约束延迟设置延迟检验模式SQL>ALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SEX_CHECK 3 INITIALLY DEFERRED；设置为立即检验模式SQL>ALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SEX_CHECK 3 INITIALLY IMMEDIATE；13.4 维护约束维护约束l修改约束延迟修改约束延迟 执行下面的命令将当前事务的所有可延迟约束设置立即检验模式 SQL>SET CONSTRAINTS ALL IMMEDIATE； 执行下面的命令将当前事务的所有可延迟约束设置延迟检验模式。

SQL>SET CONSTRAINTS ALL DEFERRED；13.4 维护约束维护约束l删除约束删除约束 【任务13.6】 删除一个无用的约束删除约束子句drop_constraint_clause的语法为：DROP | CONSTRAINT constraint | PRIMARY KEY | UNIQUE ( column [, column]... ) | CASCADE [{ KEEP | DROP } INDEX]；13.4 维护约束维护约束l删除约束删除约束 其中各关键字的含义为：ØCONSTRAINT 要删除的约束名ØPRIMARY KEY 删除的主键约束当删除主健约束时，相关的唯一性索引同时被删除ØUNIQUE 删除的唯一性约束当删除唯一性约束时，相关的唯一性索引同时被删除ØCASCADE 使用该参数可以删除引用该表主键的所有外键约束，然后删除唯一性约束和主键约束ØDROP INDEX 在删除主键约束和唯一性约束时，Oracle将删除约束所对应的索引该选项为默认值ØKEEP INDEX 如果只删除约束而保留索引，可以使用KEEP INDEX。

13.4 维护约束维护约束l删除约束删除约束 删除TEST.STUDENTS表中的STUDENTS_ID_PK主键约束SQL>ALTER TABLE TEST.STUDENTS 2 DROP CONSTRAINT STUDENTS_ID_PK 3 CASCADE；执行下列命令也可以删除STUDENTS_ID_PK主键约束SQL>ALTER TABLE TEST.STUDENTS 2 DROP PRIMARY KEY 3 CASCADE；13.4 维护约束维护约束l删除约束删除约束 在具有主键约束和外键约束的主表和子表之间执行DDL语句或DML语句时应注意以下几点：Ø在删除主表之前，必须首先删除外键约束Ø在截断（TRUNCATE）主表时，首先应禁止子表的外键 Ø在删除包含有主表的表空间时，应首先删除外键约束Ø在删除主表中的记录时，如果在DELETE语句中没有使用ON DELETE CASCADE 或ON DELETE SET NULL子句时，应确保子表中没有与主键对应的记录13.5 查询约束信息查询约束信息视 图 名 称说 明DBA_CONSTRAINTSALL_CONSTRAINTSUSER_CONSTRAINTS￿￿￿￿描述了所有约束的基本信息，包括约束的名称、类型、状态、延迟性等。

DBA_CONS_COLUMNSALL_CONS_COLUMNSUSER_CONS_COLUMNS￿￿￿￿描述了与约束相关字段的信息13.5 查询约束信息查询约束信息查询TEST.STUDENTS表中的约束信息SQL>SELECT CONSTRAINT_NAME NAME，2 CONSTRAINT_TYPE，3 STATUS，4 DEFERRABLE，5 DEFERRED，6 VALIDATED7 FROM ALL_CONSTRAINTS 8 WHERE TABLE_NAME='STUDENTS'；结果显示为：NAME C STATUS DEFERRABLE DEFERRED VALIDATED--------------------- ---- --------------- --------------------------- ------------------- ----------------------------STUDENTS_PK P DISABLED NOT DEFERRABLE IMMEDIATE VALIDATEDSEX_CHECK C ENABLED DEFERRABLE IMMEDIATE VALIDATED13.5 查询约束信息查询约束信息查询TEST.STUDENTS表中各字段的约束信息。

SQL>SELECT CONSTRAINT_NAME， 2 COLUMN_NAME 3 FROM ALL_CONS_COLUMNS 4 WHERE TABLE_NAME = 'STUDENTS'；结果显示为：CONSTRAINT_NAME COLUMN_NAME------------------------------- ---------------------------------------STUDENTS_PK IDSEX_CHECK SEX13.6 本章小结本章小结1）数据完整性是指存储在数据库中数据的一致性和正确性数据完整性分为实体完整性、域完整性、引用完整性和用户定义完整性实体完整性可以保证表内每条记录的唯一性域完整性可以保证表内数据项的合理性和有效性引用完整性又称参照完整性，可以保证引用表和被引用表之间的数据一致性用户定义完整性是指用户可以定义不属于其他任何完整性分类的特定业务规则2）Oracle允许定义和实施了NULL规则、唯一性规则、引用完整性规则和用户自定义规则，这些规则可用应用程序代码、完整性约束和数据库触发器实现。

使用完整性约束是保证数据完整性最常用的方法13.6 本章小结本章小结l在Oracle中，约束条件主要包括非空约束、唯一性约束、主键约束、外键约束和检查约束非空约束、检查约束一般为程序开发人员关心的问题，而DBA主要负责唯一性约束、主键约束和外键约束lOracle将完整性约束组合成4种状态，具体为ENABLE VALIDATE、ENABLE NOVALIDATE、DISABLE VALIDATE、DISABLE NOVALIDATE这4中状态可以对将要输入到表中的数据进行完整性约束的检查，也可以对表中已有的数据进行完整性约束的验证13.6 本章小结本章小结lOralce约束可分为非延迟约束和可延迟约束两种非延迟约束也叫立即约束，是在一条DML语句执行完后立即进行完整性约束检查，如果发现数据不满足约束条件，则回退该操作可延迟约束是指可以指定约束检查的时间，可以在DML语句执行完后不立即进行约束检查，而是延迟到事务提交命令COMMIT执行时检查如果检查数据不符合约束条件，则Oracle回退整个事务操作13.6 本章小结本章小结lOracle的完整性约束可以在创建表时定义，也可以在修改表时增加新的约束，在定义约束的同时设置约束的状态和检查时间。

作用于单个列上的约束称之为列级约束，作用在一个表中多个列上的约束叫表级约束列级约束在定义列的子句中设置，表级约束必须在表一级设置约束子句l维护约束包括增加一个新约束、修改已有约束的状态、重新命名以及删除约束维护约束语句为ALTER TABLE。