默认情况下，完整性约束在整个语句得到处理后进行检查。
但是可以创建延迟约束，把这个约束延迟到COMMIT或另外某个时间才检查。可以指定为
·DEFERRABLE INITIALLY IMMEDIATE：创建可延迟的约束，但是初始状态是在语句级检查。INITIALLY IMMEDIATE也可以不写。
·DEFERRABLE INITIALLY DEFERRED: 创建可延迟的约束，而且初始状态是在延迟检查。
例如：
create table t
( x int constraint x_not_null not null,
y int constraint y_not_null not null deferrable initially immediate,
z int constraint z_not_null not null deferrable initially deferred
);
然后执行下面语句，观察结果：
view plain
tony@ORA11GR2> insert into t values(1, 2, null);
1 row created.
tony@ORA11GR2> set constraint z_not_null immediate;
set constraint z_not_null immediate
*
ERROR at line 1:
ORA-02290: check constraint (TONY.Z_NOT_NULL) violated
tony@ORA11GR2> insert into t values(1, null, null);
insert into t values(1, null, null)
*
ERROR at line 1:
ORA-02290: check constraint (TONY.Y_NOT_NULL) violated
tony@ORA11GR2> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (TONY.Z_NOT_NULL) violated
但是需要注意，应当在只有真正需要的时候才创建延迟约束。延迟约束会在物理实现上引入不易觉察的差别。
例如，还是对上面创建的表格T，在列X,Y,Z上分别创建索引，然后执行select count(*)操作，察看各自的执行计划。
view plain
tony@ORA11GR2> insert into t values(1, 2, 3);
1 row created.
tony@ORA11GR2> set autotrace on explain
tony@ORA11GR2> create index t_idx on t(x);
Index created.
tony@ORA11GR2> select count(*) from t;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 995313729
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| T_IDX | 1 | 1 (0)| 00:00:01 |
------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
tony@ORA11GR2> drop index t_idx;
Index dropped.
tony@ORA11GR2> create index t_idx on t(y);
Index created.
tony@ORA11GR2> select count(*) from t;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
tony@ORA11GR2> drop index t_idx;
Index dropped.
tony@ORA11GR2> create index t_idx on t(z);
Index created.
tony@ORA11GR2> select count(*) from t;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------