ORACLE约束的属性   今天处理了一个由于约束插入数据失败的问题，处理时感到有些吃力，三天不练手生啊。在这里回忆一下。 Oracle数据库Constraint约束的四对属性：Deferrable/not deferrable, Deferred/immediate, enable/disable, validate/novalidate。   www.2cto.com   准备一下环境： SQL> create table empx as select * from emp;   SQL> create table deptx as select * from dept;   SQL> alter table empx add constraint pk_empx primary key(empno);   SQL> alter table deptx add constraint pk_deptx primary key(deptno);   SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno);   SQL> select * from deptx;   [plain]     DEPTNO DNAME                   LOC   --------- ------------------------------------------ ---------------------------------------    www.2cto.com   10 ACCOUNTING                     NEW YORK   20 RESEARCH                   DALLAS   30 SALES                      CHICAGO   40 OPERATIONS                     BOSTON     SQL> select * from empx;   [plain]       EMPNO ENAME              JOB                    MGR HIREDATE       SAL   COMM     DEPTNO   ---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------         7369 SMITH              CLERK                 7902 17-DEC-80      800            20         7499 ALLEN              SALESMAN              7698 20-FEB-81         1600    300         30         7521 WARD               SALESMAN              7698 22-FEB-81         1250    500         30         7566 JONES              MANAGER               7839 02-APR-81         2975            20         7654 MARTIN             SALESMAN              7698 28-SEP-81         1250   1400         30         7698 BLAKE              MANAGER               7839 01-MAY-81         2850            30         7782 CLARK              MANAGER               7839 09-JUN-81         2450            10         7788 SCOTT              ANALYST               7566 19-APR-87         3000            20         7839 KING               PRESIDENT              17-NOV-81         5000            10         7844 TURNER             SALESMAN              7698 08-SEP-81         1500      0         30         7876 ADAMS              CLERK                 7788 23-MAY-87         1100            20         7900 JAMES              CLERK                 7698 03-DEC-81      950            30         7902 FORD               ANALYST               7566 03-DEC-81         3000            20         7934 MILLER             CLERK                 7782 23-JAN-82         1300            10      14 rows selected.     SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';   [plain]  CONSTRAINT_NAME      COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED   ------------------------ ---------------- ------------------------ ---------------- ---------------- ------------   PK_EMPX                   ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED   FK_DEPTX                  ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED     SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50); insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50) * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found   这里通过emp和dept表创建了两张表empx和deptx,并在empx表上创建了外键约束fk_deptx。   1.Deferrable,not deferrable(default value)   1)这对属性是定义是否可以延时验证,deferrable是指作验证时机,如果在commit的时check为deferrable,否则为immediate .只有在设置Deferrable才可以设置另一属性Deferred,immediate.   www.2cto.com   2)这对属性是在创建的constraint的时候定义的,不能被修改.   notice:如果建立了Deferrable的uk或pk,只会建立相应的nonuniquce index,而不会建立uniquce index   SQL> alter table empx drop constraint fk_deptx;   SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable;   SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';   [plain]  CONSTRAINT_NAME      COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED   ------------------------ ---------------- ------------------------ ---------------- ---------------- ------------   PK_EMPX                   ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED   FK_DEPTX                  ENABLED          DEFERRABLE       IMMEDIATE        VALIDATED     SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50); insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50) * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found   在会话级设置： SQL> set constraint FK_DEPTX deferred;   SQL>  select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';   [plain]  CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED            VALIDATED   ---------------- ---------------- ------------------------ ---------------- --------------------------- ----------------   PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE           VALIDATED   FK_DEPTX              ENABLED          DEFERRABLE       IMMEDIATE           VALIDATED     SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50);   1 row created.   SQL> commit; commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found   单独deferrable的含义就是是否允许约束检查延后进行。单独设置deferrable为deferrable之后，约束检查延后是不可以直接使用的，要配合deferred参数，如果该参数是immediate，那么约束还是在DML的时候进行应用。如果deferred参数设置为deferred，约束就是在事务commit提交的时候应用，出现错误就连带回滚rollback整个事务。 2.Deferred,immediate(default value)   www.2cto.com   deferred属性是配合deferrable属性使用的。当deferrable设置为deferrable之后，可以通过set constraints在会话级进行deferred属性的设置，来确定约束应用时点。 定义约束是使用initially关键字来确定约束的deferred属性取值。   SQL> alter table empx drop constraint fk_deptx;   SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred;   SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';   [plain]  CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED            VALIDATED   ---------------- ---------------- ------------------------ ---------------- --------------------------- ----------------   PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE           VALIDATED   FK_DEPTX              ENABLED          DEFERRABLE       DEFERRED            VALIDATED     SQL>  insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50); 1 row created.   SQL> commit; commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found   可以通过创建contraint时指定Deferred值，也可以通过会话级别的语句SET CONSTRAINT(s)来设置（如上面的set constraint FK_DEPTX deferred）。   当属性DEFERRABLE和DEFERRED的值分别为DEFERRABLE和DEFERRED时，在事务提交时才验证，如果验证没通过，则报错，事务回滚。   3. novalidate, validate(default value) 定义是否对表中已经存在的数据作检查。   删除约束并插入一条脏数据： SQL> alter table empx drop constraint fk_deptx;   Table altered. SQL>  insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50);   1 row created.   SQL> commit;   www.2cto.com   Commit complete.   SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno); alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred                                 * ERROR at line 1: ORA-02298: cannot validate (SCOTT.FK_DEPTX) - parent keys not found   SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred; alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred                                 * ERROR at line 1: ORA-02298: cannot validate (SCOTT.FK_DEPTX) - parent keys not found   语句中不带validate属性参数，默认创建的是validate的约束。由于有脏数据的存在，不管是创建延时还是非延时的约束都不成功。   SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) novalidate;   Table altered.   创建novalidate的约束成功。   SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';   [plain]  CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED   ---------------- ---------------- ------------------------ ---------------- ---------------- ----------------   PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED   FK_DEPTX              ENABLED          NOT DEFERRABLE   IMMEDIATE        NOT VALIDATED     SQL> select * from empx; [plain]  EMPNO ENAME           JOB                    MGR HIREDATE       SAL   COMM     DEPTNO   ----- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------    7369 SMITH           CLERK                 7902 17-DEC-80      800            20    7499 ALLEN           SALESMAN              7698 20-FEB-81         1600    300         30    7521 WARD            SALESMAN              7698 22-FEB-81         1250    500         30    7566 JONES           MANAGER               7839 02-APR-81         2975            20    7654 MARTIN              SALESMAN              7698 28-SEP-81         1250   1400         30    7698 BLAKE           MANAGER               7839 01-MAY-81         2850            30    7782 CLARK           MANAGER               7839 09-JUN-81         2450            10    7788 SCOTT           ANALYST               7566 19-APR-87         3000            20    7839 KING            PRESIDENT              17-NOV-81         5000            10    7844 TURNER              SALESMAN              7698 08-SEP-81         1500      0         30    7876 ADAMS           CLERK                 7788 23-MAY-87         1100            20    7900 JAMES           CLERK                 7698 03-DEC-81      950            30    7902 FORD            ANALYST               7566 03-DEC-81         3000            20    7934 MILLER              CLERK                 7782 23-JAN-82         1300            10    8000 TOM                 SALESMAN              7839 15-JUL-12         2000            50     有一条脏数据存在，看看能不能再增加一条： SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8001,'TOM','SALESMAN',7839,sysdate,2000,50); insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8001,'TOM','SALESMAN',7839,sysdate,2000,50) *  www.2cto.com   ERROR at line 1: ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found 不能增加违反约束的数据，novalidate的约束只是对原来的数据库不做验证，但对新插入的数据还是要进行验证的。   4. disable, enalbe(default value)   启用和禁用constraint.如果在新建pk和uk时定义了disable,将不建立相应的索引. SQL> alter table empx disable constraint fk_deptx; Table altered.   SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';   [plain]  CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED   ---------------- ---------------- ------------------------ ---------------- ---------------- ----------------   PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED   FK_DEPTX              DISABLED         NOT DEFERRABLE   IMMEDIATE        NOT VALIDATED   SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8001,'TOM','SALESMAN',7839,sysdate,2000,50);   1 row created.   SQL> commit;   Commit complete.   禁用了约束后，脏数据也能插入成功。   SQL> alter table empx enable constraint fk_deptx; alter table empx enable constraint fk_deptx                                    * ERROR at line 1: ORA-02298: cannot validate (SCOTT.FK_DEPTX) - parent keys not found   SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';   [plain]  CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED    www.2cto.com   ---------------- ---------------- ------------------------ ---------------- ---------------- ----------------   PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED   FK_DEPTX              DISABLED         NOT DEFERRABLE   IMMEDIATE        NOT VALIDATED     SQL> alter table empx enable novalidate constraint fk_deptx;   Table altered.   SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';   [plain]  CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED   ---------------- ---------------- ------------------------ ---------------- ---------------- ----------------   PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED   FK_DEPTX              ENABLED          NOT DEFERRABLE   IMMEDIATE        NOT VALIDATED     如果有脏数据，启用约束时要同时设置novalidate。   www.2cto.com   由上面的实验得知，默认情况下，Oracle constraint是不开启延迟约束和原有数据保留验证的。   下列情况下可以使用这些特性： 1）批量数据导入、物化视图刷新的时候，事务量很大，而且存在数据规律前后颠倒的情况 此时，如果开启着立即约束应用的开关，可能存在一些暂时性的约束不满足的情况，从而影响到整个系统的运行。开启约束延迟验证，就可以帮助我们解决这个问题； 2）在历史数据移植 历史数据存在不规则的情况，很多时候难以满足我们新系统的数据完整性要求。可以使用not validate的方式，对历史数据不进行约束控制，而只针对新数据开启验证。     作者 ilovemilk