ORACLE中，约束分deferred 跟 immediate 2种：

deferred:如果 Oracle 在事务提交（commit）时才对约束执行检查，则称此约束是延迟的（deferred）。如果数据违反了延迟约束，提交操作将导致事务被回滚（undo）。  immediate:如果约束是即时的（immediate）（非延迟的），则此约束将在语句执行结束后进行检查。如果数据违反了延迟约束，语句将被立即回滚。

 

解释一下，即：将约束检验延迟到了执行COMMIT的时候。以下是具体的解释和举例。 定义为可延迟（deferrable）的约束可以指定为：

1. initially immediate（初始化立即执行）或

2. initially deferred（初始化延迟执行）。

解释二者的区别。初始化立即执行/延迟执行规定了在默认情况下应该如何执行约束：

 

初始化立即执行--在每条语句执行结束时检验约束

初始化延迟执行--一直等到事务完成后（或者调用set constraint immediate语句时）才检验约束

一般情况下，我们用的约束初始都是immediate型的(默认)，而且不好转为deferred型。但是如果初始是deferrable(需要手动指定),那deferred跟immediate 2种状态可以随意转换。 此外，约束有以下4种状态： ENABLE（启用）确保所有输入的数据都遵从约束（constraint）  DISABLE（禁用）总是允许输入数据，无论数据是否遵从约束  VALIDATE（验证）确保已存在的数据遵从约束  NOVALIDATE（无验证）允许已存在的数据不遵从约束

ENABLE VALIDATE 与 ENABLE 相同。Oracle 将检查约束，并保证所有数据均遵从约束。

ENABLE NOVALIDATE 表示所有新插入或被修改的数据都必须遵从约束，但允许已存在的数据不遵从约束。 DISABLE NOVALIDATE 与 DISABLE 相同。Oracle 不会检查约束. DISABLE VALIDATE 将禁用约束，移除约束使用的索引，并禁止修改约束键的数据。

约束不论哪种类型，要能够生效，必须状态是enable才行。

--deferred 跟 immediate的对比试验-----------------------------

SQL> drop table aa purge;

Table dropped.

SQL> create table aa ( id number,name varchar2(20),constraint pk primary key(id));

Table created.

SQL> col constraint_name for a11

SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME ,STATUS,DEFERRABLE,DEFERRED,validated from u ser_constraints where table_name='AA';

CONSTRAINT_ C TABLE STATUS   DEFERRABLE     DEFERRED VALIDATED

----------- - ----- -------- -------------- --------- ------------- PK          P AA    ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

--可以看到，默认的是NOT DEFERRABLE，下面我们将它转为immediate试试

SQL> set constraint pk immediate;

set constraint pk immediate * ERROR at line 1: ORA-02447: cannot defer a constraint that is not deferrable

--增加一个uk，指定deferrable initially immidiate|deferred

SQL> alter table aa add constraint uk unique (name) deferrable initially immediate;

Table altered.

SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME ,STATUS,DEFERRABLE,DEFERRED,validated from u

ser_constraints where table_name='AA';

CONSTRAINT_ C TABLE STATUS   DEFERRABLE     DEFERRED VALIDATED

----------- - ----- -------- -------------- --------- ------------- PK          P AA    ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED UK          U AA    ENABLED DEFERRABLE     IMMEDIATE VALIDATED

SQL> select * from aa;

no rows selected

SQL> insert into aa values(1,'SDF');

1 row created.

SQL> insert into aa values(2,'SDF');

insert into aa values(2,'SDF') * ERROR at line 1: ORA-00001: unique constraint (LYN.UK) violated

SQL> set constraints uk deferred;

Constraint set.

SQL> select * from aa;

no rows selected

SQL> insert into aa values(1,'SDF');

1 row created.

SQL> insert into aa values(2,'SDF');

1 row created.

SQL> commit;

commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-00001: unique constraint (LYN.UK) violated

此外，可以在SESSION里设定所有约束是立即检查(immediate)还是延迟检查(deferred)，当然，这只影响初始时指定可延迟的(deferrable)约束。

SQL> alter session set constraint=immediate;

Session altered.

SQL> alter session set constraints=deferred;

Session altered.

--4种状态对比-----------------

SQL> drop table aa purge;

Table dropped.

SQL> create table aa(id number primary key);

Table created.

--enable validate(默认)

SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME ,STATUS,DEFERRABLE,DEFERRED,validated from

user_constraints where table_name='AA';

CONSTRAINT_ C TABLE STATUS   DEFERRABLE     DEFERRED VALIDATED

----------- - ----- -------- -------------- --------- ------------- SYS_C002829 P AA    ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

SQL> insert into aa select 2010 from dual;

1 row created.

SQL> insert into aa select 2010 from dual;

insert into aa select 2010 from dual * ERROR at line 1: ORA-00001: unique constraint (LYN.SYS_C002829) violated

--disable novalidate

SQL> alter table aa modify constraints SYS_C002829 disable;

Table altered.

SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME ,STATUS,DEFERRABLE,DEFERRED,validated from

user_constraints where table_name='AA';

CONSTRAINT_ C TABLE STATUS   DEFERRABLE     DEFERRED VALIDATED

----------- - ----- -------- -------------- --------- ------------- SYS_C002829 P AA    DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED

SQL> select * from aa;

        ID

----------       2010

SQL> insert into aa select 2010 from dual;

1 row created.

SQL> insert into aa select 2010 from dual;

1 row created.

SQL> commit;

Commit complete.

--disable validate

SQL> alter table aa modify constraints SYS_C002829 disable validate;

alter table aa modify constraints SYS_C002829 disable validate * ERROR at line 1: ORA-02437: cannot validate (LYN.SYS_C002829) - primary key violated

SQL> select * from aa;

        ID

----------       2010       2010       2010

SQL> delete aa where rownum<3;

2 rows deleted.

SQL> select rownum,id from aa;

    ROWNUM         ID

---------- ----------          1       2010

SQL> alter table aa modify constraints SYS_C002829 disable validate;

Table altered.

SQL> update aa set id=2012;

update aa set id=2012 * ERROR at line 1: ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabled and validated

SQL> insert into aa select 2012 from dual; insert into aa select 2012 from dual * ERROR at line 1: ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabled and validated

SQL> delete from aa; delete from aa * ERROR at line 1: ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabled and validated

SQL> truncate table aa; truncate table aa * ERROR at line 1: ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabled and validated

--enable novalidate

SQL> alter table aa enable novalidate constraint SYS_C002829;

Table altered.

SQL> insert into aa select 2010 from dual;

insert into aa select 2010 from dual * ERROR at line 1: ORA-00001: unique constraint (LYN.SYS_C002829) violated