学步园

默认情况下，完整性约束在整个语句得到处理后进行检查。
但是可以创建延迟约束，把这个约束延迟到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
);
然后执行下面语句，观察结果：

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(*)操作，察看各自的执行计划。

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

-------------------------------------------------------------------
| 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)

可以看到，优化器不会使用在列Y,Z上建立的索引来查询行数。实际上也不能使用，这是因为B*Tree索引不会为完全为NULL的索引键建立条目，Y和Z虽然加了不为NULL的约束，但是是延迟约束，也就是允许临时为NULL，所以优化器无法使用它们来查询行数。

还有，如果创建1个unique或者primary key约束，oracle会为这个约束创建1个唯一索引。但是如果创建1个延迟unique或者primary key约束，由于可以临时忽略约束，oracle就只能创建1个非唯一索引。
例如：

tony@ORA11GR2> drop table t;

Table dropped.

tony@ORA11GR2> create table t(x int primary key);

Table created.

tony@ORA11GR2> select index_name, uniqueness from user_indexes where table_name='T';

INDEX_NAME                                                   UNIQUENESS
------------------------------------------------------------ ------------------
SYS_C0011412                                                 UNIQUE

tony@ORA11GR2> drop table t;

Table dropped.

tony@ORA11GR2> create table t(x int primary key deferrable);

Table created.

tony@ORA11GR2> select index_name, uniqueness from user_indexes where table_name='T';

INDEX_NAME                                                   UNIQUENESS
------------------------------------------------------------ ------------------
SYS_C0011413                                                 NONUNIQUE

 Deferrable约束的用途

1 物化视图

物化视图（快照），这是它的主要用途。这些视图会使用延迟约束来进行视图刷新。
在刷新物化视图的过程中，可能会破坏完整性，而且将不能逐句检验约束。但到执行COMMIT时，数据完整性就没问题了，而且能满足约束。没有延迟约束，物化视图的约束可能会使刷新过程不能成功进行。

2 级联更新

需要更新父/子关系中的主键时，它有助于级联更新。