维护数据完整性..ppt

OOracleracle体系结构与管理体系结构与管理第十三章第十三章 维护数据完整性维护数据完整性 本章要点本章要点数据完整性概述数据完整性概述 完整性约束完整性约束 实现约束实现约束 维护约束维护约束 查询约束信息查询约束信息 本章教学目标本章教学目标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（SALARYSET 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|NOVALIDATE13.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 定义列级约束定义列级约束SQLCREATE 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定义表级约束定义表级约束 SQLCREATE 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 ALTER TABLE TEST.STUDENTS 2 ADD(CONSTRAINT SCORE_CHECK 3 CHECK(score=0 and scoreALTER TABLE TEST.STUDENTS 2 ADD(CONSTRAINT SCORE_CHECK3 CHECK(score=0 and scoreALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 ENABLE；结果显示为：表已更改。13.4 维护约束维护约束l修改约束状态修改约束状态修改约束状态为验证状态：SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 VALIDATE；结果显示为：表已更改。13.4 维护约束维护约束l修改约束状态修改约束状态修改约束状态为非验证状态：SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SCORE_CHECK 3 NOVALIDATE；结果显示为：表已更改。13.4 维护约束维护约束l修改约束状态修改约束状态设置约束状态为ENABLE VALIDATE：SQLALTER 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约束，并设置该约束为验证状态：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 维护约束维护约束l修改约束延迟修改约束延迟 【任务13.5】为表STUDENTS的SEX字段定义一个可延迟约束，使之只能为1或0，并设置其为立即检验模式或延迟检验模式。13.4 维护约束维护约束l修改约束延迟修改约束延迟为表TEST.STUDENTS的SEX定义一个非延迟约束。SQLALTER TABLE TEST.STUDENTS 2 ADD（CONSTRAINT SEX_CHECK 3 CHECK(SEX=0 or SEX=1)4 NOT DEFERRABLE）；结果显示为：表已更改。13.4 维护约束维护约束l修改约束延迟修改约束延迟设置延迟检验模式。SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SEX_CHECK 3 INITIALLY DEFERRED；设置为立即检验模式。SQLALTER TABLE TEST.STUDENTS 2 MODIFY CONSTRAINT SEX_CHECK 3 INITIALLY IMMEDIATE；13.4 维护约束维护约束l修改约束延迟修改约束延迟 执行下面的命令将当前事务的所有可延迟约束设置立即检验模式。SQLSET CONSTRAINTS ALL IMMEDIATE；执行下面的命令将当前事务的所有可延迟约束设置延迟检验模式。SQLSET 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主键约束。SQLALTER TABLE TEST.STUDENTS 2 DROP CONSTRAINT STUDENTS_ID_PK 3 CASCADE；执行下列命令也可以删除STUDENTS_ID_PK主键约束。SQLALTER 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表中的约束信息。SQLSELECT 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 VALIDATED13.5 查询约束信息查询约束信息查询TEST.STUDENTS表中各字段的约束信息。SQLSELECT CONSTRAINT_NAME，2 COLUMN_NAME 3 FROM ALL_CONS_COLUMNS 4 WHERE TABLE_NAME=STUDENTS；结果显示为：CONSTRAINT_NAME COLUMN_NAME-STUDENTS_PK IDSEX_CHECK SEX13.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。