数据完整性的维护

数据完整性的维护 数据库设计中有一个重要的环节就是建立数据模型来体现业务流程和规则。数据完整性就是业务规则的一种体现，是业务流程能够正常运转的保证。如何来实现数据的完整性呢？主要可以通过以下三个途径来实现： （1） 应用程序代码实现 （2） 数据库的触发处理 （3） 数据完整性约束的定义 应用程序代码实现： 指通过数据库的后台程序或客户端的应用程序来实现 数据库的触发处理： 指当一个事件（比如INSERT一条记录或UPDATE一个表的某个字段）产生时，一个和这个事件相关的PL/SQL程序是否可以执行。数据库触发器一般是应用于复杂业务规则中，因为数据完整性约束不好定义。 下面是这篇文章主要要讲解的数据完整性约束的定义 使用数据完整性约束的优越性： （1） 提供优良的性能 （2） 不需要编码，容易定义和修改 （3） 集中式管理 （4） 灵活性高（可以DISABLED或ENABLED） （5） 在数据字典中有完整的记录 约束的种类： （1） NOT NULL （2） UNIQUE （3） PRIMARY KEY：一个表只能有一个主键，相当于NOT NULL+UNIQUE （4） CHECK （5） FOREIGN KEY 约束的状态： （1） DISABLE NOVALIDATE ：不检查表中数据的完整性，同时新增的数据是否符合约束也不做检查，缺省状态 （2） DISABLE VALIDATE ： 任何对于该约束的修改都是不允许的

SQL> alter table b2 disable novalidate constraints b2_con; 表已更改。 SQL> insert into b2 (b) values('10'); insert into b2 (b) values('10') ERROR 位于第 1 行: ORA-25128: 不能对带有禁用和验证约束条件 (SYS.B2_CON) 的表进行插入/更新/删除

（3） ENABLE NOVALIDATE ：任何违反约束的新数据都不能执行，但对表中已有违反约束的数据不做检查。 （4） ENABLE VALIDATE ： 任何违反约束的新数据都不能执行，并对表中已有的数据的合法性进行检查，缺省状态。如果一个约束从DISABLE状态直接变成ENALBE VALIDATE状态，则这个表将锁住并检查数据的合法性，这将影响DML操作，造成等待。所以应该先从DISABLE状态改变成ENABLE NOVALIDATE状态，再改变成ENABLE VALIDATE状态。 注意点： 当一个UNIQUE，PARIMAY KEY约束从ENABLE转变成DISABLE状态，则这个约束上的UNIQUE索引（自动创建的或手工创建的，如果先手工创建了一个索引，则创建约束时不再自动创建索引了，而直接应用手工创建的索引）DROP了。如果从DISABLE状态转变成ENABLE状态，数据库会自动以约束名创建一个UNIQUE索引。 如果CONSTRAINTS是DEFERRED，则索引不会DROP掉。当CONSTRAINTS创建时是DEFERRED，则自动创建的索引是NONUNIQUE。 SQL> create table test1 (a varchar2(10),b varchar2(10)); 表已创建。

SQL> create unique index test1_inx on test1(a); 索引已创建。

SQL> alter table test1 add constraints test1_con primary key(a,b); 表已更改。

SQL> select index_name from dba_indexes where table_name='TEST1'; INDEX_NAME ------------------------------ TEST1_INX TEST1_CON

SQL> ALTER TABLE TEST1 DISABLE constraints test1_con; 表已更改。

SQL> / INDEX_NAME ------------------------------ TEST1_INX

SQL> ALTER TABLE TEST1 enable constraints test1_con; 表已更改。

SQL> / INDEX_NAME ------------------------------ TEST1_INX TEST1_CON 约束的检查方式： （1） Immediate constraints ：应用于每条DML语句，不可应用在一个事务处理之后 （2） Deferred constraints ： 应用在事务处理中，在事务提交之前仅仅是检查，如果在递交的时候，发现有不合法数据，整个事务回退。 设置约束检查方式的语法： （1） SET CONSTRAINTS语句：可以指定具体某个约束或所有的。不可以在TRIGGER中使用 （2） ALTER SESSION 语句： 设置所有的约束，应用对象是当前会话 SQL: ALTER SESSION SET CONSTRAINT[S] = {IMMEDIATE|DEFERRED|DEFAULT}

SQL*PLUS: SET CONSTRAINT | CONSTRAINTS {constraint |ALL } {IMMEDIATE|DEFERRED}

约束创建语句： （1）字段级的 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) } constraint_state :== [NOT DEFERRABLE|DEFERRABLE [INITIALLY {IMMEDIATE|DEFERRED}] ] [DISABLE|ENABLE [VALIDATE|NOVALIDATE]]

（2）表级的，NOT NULL约束不能在表级约束子句中定义 [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) } [constraint_state]

例如： SQL> create table test1 (a varchar2(10),b varchar2(10), constraint test_con prim ary key(a),constraint test_con1 unique(b));

表已创建。 （3）表创建好后，还可以用ALTER TABLE语句来增加约束 SQL> alter table test1 add constraints test1_con primary key(a) deferrable initi ally deferred; 表已更改。 SQL> alter table test1 modify b constraint test1_con1 not null; 表已更改。

关于约束创建时注意点： （1） 索引和表应该创建在不同的表空间中，可以通过USING INDEX子句或通过CREATE TABLE，CREATE INDEX，ALTER TABLE ADD CONSTRAINT来创建 （2） 如果频繁的装载数据到表中，DISABLE CONSTRAINT会使性能更好，装载数据后，再ENABLE CONSTRAINTS。可以通过DEFERRED CONSTRAINT来自动创建非唯一性索引来提高性能。

ENABLE CONSTRAINTS 有两种方法可以ENABLE CONSTRAINTS （1） ENABLE NOVALIDATE: 因为PRIMARY KEY或UNIQUE约束会创建索引，并且当该约束是DEFERRED的，则NOVALIDATE会比VALIDATE快很多，因为存在的数据不会做检查的，不会在表上产生LOCK的。 注意点： 使用ENALBE NOVALIDATE时，如果要应用索引，则约束必须是DEFERRED的，否则就没有优越性了。因为ORACLE会锁住表去创建索引的。 （2） ENABLE VALIDATE： 表被锁住直到存在数据的合法性检查完成，如果没有索引的话，ORACLE还要自动创建唯一性索引为PRIMARY KEY或UNIQUE，或创建非唯一性索引为DEFERRED的PRIMARY KEY或UNIQUE 从NOVALIDATE转到VALIDATE的优越性 （1） 不会产生表锁定 （2） 所有的约束是同时ENABLED （3） 每个约束内部是并行处理的 （4） 表上同时活动的事务的允许的。 语法： ALTER TABLE [ schema. ] table ENABLE [ VALIDATE ]{CONSTRAINT constraint