维护数据完整性..ppt

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

2、ence Integrity) 用户定义完整性(User Defined Integrity),13.1 数据完整性概述,数据完整性 作用： 实体完整性可以保证表内每条记录的唯一性。 域完整性可以保证表内数据项的合理性和有效性。 引用完整性又称参照完整性，可以保证引用表和被引用表之间的数据一致性。 用户定义完整性是指用户可以定义不属于其他任何完整性分类的特定业务规则。,13.1 数据完整性概述,数据完整性规则 Oracle应用于关系数据库数据完整性有下列4种类型的规则： NULL规则 在插入或修改表的行时是否允许包含有NULL的值。 唯一性规则 保证插入或修改的记录在字段值上的唯一性。 引用完

3、整性规则 保证多个相关表的一致性。 用户自定义规则 可实现复杂的完整性检查。,13.1 数据完整性概述,数据完整性规则 数据完整性的实现方法： 应用程序代码 完整性约束 数据库触发器,13.1 数据完整性概述,数据完整性规则 利用完整性约束实施数据完整性规则有下列优点： 创建或修改表时直接通过SQL语句定义完整性约束，无需额外的编程，可减少程序性错误。 完整性约束存储在数据字典中，任何进入表中的数据都必须接受完整性约束，可以保证数据库中所有数据的完整性。 使用完整性约束可以分离数据和程序。 DBA可以通过约束管理语句灵活地设置完整性约束的有效性。 由于完整性约束存储在数据字典中，DBA、开发人

4、员和应用程序可以快速查询事务规则，选择正确的操作或数据。,13.2 完整性约束,约束条件 非空约束（NOT NULL） 唯一性约束（UNIQUE） 主键约束（PRIMARY KEY） 外键约束（FOREIGN KEY） 检查约束（CHECK）,13.2 完整性约束,约束条件 通过EMPLOYEE表的创建说明各种约束条件的作用。 CREATE TABLE EMPLOYEE （EMPNO NUMBER（10） PRIMARY KEY， NAME VARCHAR2（40） NOT NULL， SEX CHAR（1）， DEPTNO NUMBER（2） DEFAULT 10， SALARY NUMBE

5、R（7，2） CHECK（SALARY1000000.00）， SOCNUM CHAR(14) UNIQUE， FOREIGN KEY （DEPTNO） REFERENCES DEPT（DEPTNO） TABLESPACE USERS；,13.2 完整性约束,约束状态 1.激活和禁用 激活状态（ENABLE） 在这种状态下，完整性约束被激活，Oracle将对输入到表中的数据进行完整性约束检查，如果数据不符合约束，则不允许保存到表中，所执行的操作被回退。一般情况下，完整性约束总是处于被激活状态以保证数据完整性。 禁用状态（DISABLE） 在这种状态下，完整性约束被禁止，Oracle不对输入到表

6、中的数据进行完整性约束检查，任何数据都可以存入表中。,13.2 完整性约束,约束状态 2.验证和非验证 验证状态（VALIDATE） 在这种状态下，在定义或激活约束时，Oracle检查表中该列中的数据是否满足约束条件。 非验证状态（NOVALIDATE） 在这种状态下，在定义或激活约束时，Oracle不检查表中该列中的数据是否满足约束条件。,13.2 完整性约束,约束状态 根据以上情况可以将完整性约束组合成4种状态 ： ENABLE VALIDATE 表中的数据必须满足约束条件，将要输入的数据也要满足约束条件。 ENABLE NOVALIDATE Oracle不对表中的数据进行约束验证，只对将

7、要输入的数据进行约束检查。 DISABLE VALIDATE 约束被禁用，所有将要输入的数据均不作约束检查，并且不允许作任何插入、删除或修改操作。 DISABLE NOVALIDATE 约束禁用，并且不对表中的数据进行约束验证。,13.2 完整性约束,约束检查的时间 通常情况下，Oracle在一条DML语句执行结束后立即进行完整性约束检查，如果发现数据不满足约束条件，则回退该操作。但是，有时不希望执行完一条DML语句后立即检查约束，而是多条DML语句执行完后再进行检查，这时可以在事务提交（COMMIT命令）时统一检查约束，如果任何一个数据不满足约束条件，Oracle将回退整个事务。,13.2

8、完整性约束,约束检查的时间 1.非延迟约束 非延迟约束也叫立即约束，是在一条DML语句执行完后立即进行完整性约束检查，如果发现数据不满足约束条件，则回退该操作。非延迟约束一经定义，不允许将检查时间修改在提交事务的时候，除非删除该约束，重新定义约束为延迟约束。 非延迟约束是约束的默认值，如果要显式定义非延迟约束可以在定义约束时使用NO DEFERRABLE子句。,13.2 完整性约束,约束检查的时间 2.可延迟约束 可延迟约束是指可以指定约束检查的时间，可以在DML语句执行完后不立即进行约束检查，而是延迟到事务提交命令COMMIT执行时检查。 可延迟约束是在定义约束时指定了DEFERRABLE子

9、句。该子句有两种检验模式： INITIALLY IMMEDIATE 立即检验模式。这种模式同非延迟约束一样，是在DML语句执行完后立即检查约束。这是可延迟约束的缺省值。 INITIALLY DEFERRED 延迟检验模式。这种模式不在DML语句执行完后立即检查约束，而是推迟到事务提交命令COMMIT执行完后。,13.2 完整性约束,约束检查的时间 2.可延迟约束 可延迟约束一经定义，不允许改变为非延迟约束，除非删除该约束，重新定义约束为非延迟约束。但是通过改变可延迟的检验模式可以达到非延迟约束的效果。设置验证模式可以执行SET CONSTRAINT命令实现，如： SQLSET CONSTRAI

10、NT ALL IMMEDIATE； 该命令将所有可延迟约束设置为立即检验模式。,13.2 完整性约束,约束检查的时间 2.可延迟约束 设置当前会话级的检验模式，可以执行下列命令： ALTER SESSION SET CONSTRAINT =IMMEDIATE|DEFERRED|DEFAULT； DEFAULT表示保持原来的检验模式。,13.3 实现约束,定义列级约束 【任务13.1】创建STUDENTS表时，对表中各字段设置完整性约束，如ID字段定义为该表的主键，并且设置为可延迟约束，约束为激活状态，NAME、SEX、COURSE和SCORE字段定义NOT NULL约束。,13.3 实现约束,

11、定义列级约束 作用于单个列上的约束称之为列级约束。列级约束是在定义列的子句中设置。列级约束定义子句的语法为： 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 D

12、ISABLE|ENABLE VALIDATE|NOVALIDATE,13.3 实现约束,定义列级约束 各关键字和参数的含义如下： constraint 定义约束名。如果缺省，Oracle自动命名。 NOT NULL 定义NOT NULL约束。如果为NOT NULL，表示该列不允许有空值；如果为NULL，表示该列可以有空值。 UNIQUE 定义唯一性约束，并可以通过index_clause子句定义索引。 PRIMARY KEY 定义主键约束，并可以通过index_clause子句定义索引。 REFERENCES 定义外键约束，并指出被引用表的表名和列。 ON DELETE CASCADE 表示当

13、删除父表的数据时，将子表中与父表被删除数据相关的数据一起删除。 CHECK 定义检查约束。,13.3 实现约束,定义列级约束 各关键字和参数的含义如下： NOT DEFERRABLE 定义非延迟约束。该值是缺省值。 DEFERRABLE 定义可延迟约束。 INITIALLY IMMEDIATE 设置约束检查为立即检验模式。该值是缺省值。 INITIALLY DEFERRED 设置约束检查为延迟检验模式。 ENABLE 设置约束为激活状态。该值是缺省值。 DISABLE 设置约束为禁用状态。 VALIDATE 设置约束为验证状态。该值是缺省值。 NOVALIDATE 设置约束为非验证状态。,13

14、.3 实现约束,定义列级约束 SQLCREATE TABLE TEST.STUDENTS 2 (ID VARCHAR2(10) 3 CONSTRAINT STUDENTS_ID_PK PRIMARY KEY 4 DEFERRABLE 5 USING INDEX 6 STORAGE(INITIAL 100K NEXT 100K) 7 TABLESPACE INDEXS 8 ENABLE, 9 NAME VARCHAR2(10) NOT NULL, 10 SEX VARCHAR2(2) NOT NULL, 11 COURSE VARCHAR2(20) NOT NULL, 12 SCORE NUMB

15、ER(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 200 23 PCTINCREASE 20 24 BUFFER_POOL RECYCLE)；,13.3 实现约束,定义表级约束 【任务13.2】创建EMPLOYEE表时，对表中FIRST_NAME和LAST_NAME两个字段组合在一起设置成唯一性约束，该约束为表级约束。,13.3 实现

16、约束,定义表级约束 表级约束子句的语法为： 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 IN

17、ITIALLY IMMEDIATE|DEFERRED DISABLE|ENABLE VALIDATE|NOVALIDATE 除了不能在表级约束子句中不能定义NOT NULL约束外，该子句所有关键字和参数的含义同列级约束定义子句是一样的。,13.3 实现约束,定义表级约束 SQLCREATE TABLE TEST.EMPLOYEE 2 （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

18、（2） 7 SALARY NUMBER（7，2） CHECK（SALARY1000000.00）， 8 SOCNUM CHAR(14) UNIQUE， 9 CONSTRAINT NAME UNIQUE(FIRST_NAME，LAST_NAME) 10 DEFERRABLE 11 DISABLE VALIDATE） 12 TABLESPACE USERS；,13.4 维护约束,维护约束命令 维护约束语句的语法为： ALTER TABLE schema. table ADD out_of_line_constraint out_of_line_constraint . MODIFY CONSTRA

19、INT constraint | PRIMARY KEY | UNIQUE (column) , UNIQUE (column) . DEFERRABLE INITIALLY IMMEDIATE|DEFERRED DISABLE|ENABLE VALIDATE|NOVALIDATE RENAME CONSTRAINT old_name TO new_name drop_constraint_clause ；,13.4 维护约束,维护约束命令 维护约束命令包含了4个子句，其功能为： ADD 添加新约束子句。ADD后面跟有表级约束定义子句，添加新的约束只能是表级约束。 MODIFY 修改约束状态子

20、句。可以将现有约束的状态改为ENABLE VALIDATE、ENABLE NOVALIDATE、DISABLE VALIDATE和DISABLE NOVALIDATE。 RENAME 重新命名约束名子句。 drop_constraint_clause 删除已有约束子句。,13.4 维护约束,添加约束 【任务13.3】 在STUDENTS表中添加一个检查约束，使学生成绩不能小于0分，也不能大于100分。 为表TEST.STUDENTS的SCORE定义一个检查约束。 SQL ALTER TABLE TEST.STUDENTS 2 ADD (CONSTRAINT SCORE_CHECK 3 CHEC

21、K(score=0 and score=100)； 结果显示为： 表已更改。,13.4 维护约束,修改约束状态 【任务13.4】 对STUDENTS表中SCORE_CHECK约束的状态进行设置，使之禁用、激活、验证或非验证。 在创建该约束时将其设置为禁用状态： SQLALTER TABLE TEST.STUDENTS 2 ADD (CONSTRAINT SCORE_CHECK 3 CHECK(score=0 and score=100) DISABLE)； 结果显示为： 表已更改。,13.4 维护约束,修改约束状态 激活该约束： SQLALTER TABLE TEST.STUDENTS 2 M

22、ODIFY CONSTRAINT SCORE_CHECK 3 ENABLE； 结果显示为： 表已更改。,13.4 维护约束,修改约束状态 修改约束状态为验证状态： SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 VALIDATE； 结果显示为： 表已更改。,13.4 维护约束,修改约束状态 修改约束状态为非验证状态： SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 NOVALIDATE； 结果显示为： 表已更改。,13.4 维护约束,修改约

23、束状态 设置约束状态为ENABLE VALIDATE： SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 ENABLE VALIDATE； 结果显示为： 表已更改。,13.4 维护约束,修改约束状态 利用ALTER TABLE语句也可以修改约束的状态，其语法为： ALTER TABLE schema. table | ENABLE | DISABLE | NOVALIDATE | NOVALIDATE CONSTRAINT constraint | PRIMARY KEY | UNIQUE ( column , col

24、umn . ) USING INDEX index_clause ；,13.4 维护约束,修改约束状态 激活SCORE_CHECK约束，并设置该约束为验证状态： SQLALTER TABLE TEST.STUDENTS 2 ENABLE VALIDATE CONSTRAINT SCORE_CHECK； 要禁用SCORE_CHECK约束，并设置该约束为非验证状态： SQLALTER TABLE TEST.STUDENTS 2 DISABLE NOVALIDATE CONSTRAINT 3 SCORE_CHECK；,13.4 维护约束,修改约束延迟 【任务13.5】 为表STUDENTS的SEX字

25、段定义一个可延迟约束，使之只能为1或0，并设置其为立即检验模式或延迟检验模式。,13.4 维护约束,修改约束延迟 为表TEST.STUDENTS的SEX定义一个非延迟约束。 SQLALTER TABLE TEST.STUDENTS 2 ADD （CONSTRAINT SEX_CHECK 3 CHECK(SEX=0 or SEX=1) 4 NOT DEFERRABLE）； 结果显示为： 表已更改。,13.4 维护约束,修改约束延迟 设置延迟检验模式。 SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SEX_CHECK 3 INITIALLY DE

26、FERRED； 设置为立即检验模式。 SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SEX_CHECK 3 INITIALLY IMMEDIATE；,13.4 维护约束,修改约束延迟 执行下面的命令将当前事务的所有可延迟约束设置立即检验模式。 SQLSET CONSTRAINTS ALL IMMEDIATE； 执行下面的命令将当前事务的所有可延迟约束设置延迟检验模式。 SQLSET CONSTRAINTS ALL DEFERRED；,13.4 维护约束,删除约束 【任务13.6】 删除一个无用的约束。 删除约束子句drop_constrain

27、t_clause的语法为： DROP | CONSTRAINT constraint | PRIMARY KEY | UNIQUE ( column , column. ) | CASCADE KEEP | DROP INDEX；,13.4 维护约束,删除约束 其中各关键字的含义为： CONSTRAINT 要删除的约束名。 PRIMARY KEY 删除的主键约束。当删除主健约束时，相关的唯一性索引同时被删除。 UNIQUE 删除的唯一性约束。当删除唯一性约束时，相关的唯一性索引同时被删除。 CASCADE 使用该参数可以删除引用该表主键的所有外键约束，然后删除唯一性约束和主键约束。 DROP

28、INDEX 在删除主键约束和唯一性约束时，Oracle将删除约束所对应的索引。该选项为默认值。 KEEP INDEX 如果只删除约束而保留索引，可以使用KEEP INDEX。,13.4 维护约束,删除约束 删除TEST.STUDENTS表中的STUDENTS_ID_PK主键约束。 SQLALTER TABLE TEST.STUDENTS 2 DROP CONSTRAINT STUDENTS_ID_PK 3 CASCADE； 执行下列命令也可以删除STUDENTS_ID_PK主键约束。 SQLALTER TABLE TEST.STUDENTS 2 DROP PRIMARY KEY 3 CASCA

29、DE；,13.4 维护约束,删除约束 在具有主键约束和外键约束的主表和子表之间执行DDL语句或DML语句时应注意以下几点： 在删除主表之前，必须首先删除外键约束。 在截断（TRUNCATE）主表时，首先应禁止子表的外键。 在删除包含有主表的表空间时，应首先删除外键约束。 在删除主表中的记录时，如果在DELETE语句中没有使用ON DELETE CASCADE 或ON DELETE SET NULL子句时，应确保子表中没有与主键对应的记录。,13.5 查询约束信息,13.5 查询约束信息,查询TEST.STUDENTS表中的约束信息。 SQLSELECT CONSTRAINT_NAME NAME

30、， 2 CONSTRAINT_TYPE， 3 STATUS， 4 DEFERRABLE， 5 DEFERRED， 6 VALIDATED 7 FROM ALL_CONSTRAINTS 8 WHERE TABLE_NAME=STUDENTS； 结果显示为： NAME C STATUS DEFERRABLE DEFERRED VALIDATED - - - - - - STUDENTS_PK P DISABLED NOT DEFERRABLE IMMEDIATE VALIDATED SEX_CHECK C ENABLED DEFERRABLE IMMEDIATE VALIDATED,13.5 查询约束信息,查询TEST.STUDENTS表中各字段的约束信息。 SQLSELECT CONSTRAINT_NAME， 2 COLUMN_NAME 3 FROM ALL_CONS_COL