Constraints statement （deferrable or not）

在做047一道题目的时候遇倒了deferrable和deferrable的约束限制，现把原理摘要如下，最后附上一些例子，帮助新童鞋们理解：

如下内容来自：

Oracle Database SQL Reference
10g Release 2 (10.2)

As part of constraint definition, you can specify how and when Oracle should enforce the constraint.

constraint_state You can use the constraint_state with both inline and out-of-line specification. You can specify the clauses of constraint_state in any order, but you can specify each clause only once.

DEFERRABLE Clause The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

• Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The checking of a NOT DEFERRABLE constraint can never be deferred to the end of the transaction.

  If you declare a new constraint NOT DEFERRABLE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

• Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

You cannot alter the deferrability of a constraint. That is, whether you specify either of these parameters, or make the constraint NOT DEFERRABLE implicitly by specifying neither of them, you cannot specify this clause in an ALTER TABLEstatement. You must drop the constraint and re-create it.

Restriction on [NOT] DEFERRABLE You cannot specify either of these parameters for a view constraint.

INITIALLY Clause The INITIALLY clause establishes the default checking behavior for constraints that are DEFERRABLE. The INITIALLY setting can be overridden by a SET CONSTRAINT(S) statement in a subsequent transaction.

• Specify INITIALLY IMMEDIATE to indicate that Oracle should check this constraint at the end of each subsequent SQL statement. If you do not specify INITIALLY at all, then the default is INITIALLY IMMEDIATE.

  If you declare a new constraint INITIALLY IMMEDIATE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

• Specify INITIALLY DEFERRED to indicate that Oracle should check this constraint at the end of subsequent transactions.

This clause is not valid if you have declared the constraint to be NOT DEFERRABLE, because a NOT DEFERRABLE constraint is automatically INITIALLY IMMEDIATE and cannot ever be INITIALLY DEFERRED.

一些例子：来自博文 

/blog/325630#

Initially immediate 

This is the default when neither initially immediate nor initially deferred has been specified. 
The constraint is checked after each SQL statement. 

create table init_immediate (
  id   number primary key initially immediate,
  data varchar2(50)
);

Initially deferred 

The constraint is checked when a transaction ends. 
drop table init_immediate; 

create table init_immediate (
  id   number       primary key initially deferred,
  data varchar2(50) not null initially deferred
);


insert into init_immediate values ( null, 'one');
insert into init_immediate values (    3, null);
insert into init_immediate values (    8, 'eight');
insert into init_immediate values (    3, 'two');

update init_immediate set id = 1 where data = 'one';
update init_immediate set id = 2 where data = 'two';
update init_immediate set data = 'three' where id = 3;

commit;

It will be OK. 

ORA-02447 

A not deferrable constraint cannot be set to initially deferred, 
it raises an ORA-02447: cannot defer a constraint that is not deferrable which seems logical: 

create table no_no_ora_02447 (
  id   number primary key not deferrable initially deferred,
  data varchar2(50)
);

Deferrable constraint 

create table cons_deferrable_pk_tab (
  a number,
  b varchar2(10),
  c number not null deferrable,
  d date,
  primary key (a, b) deferrable
);

create table cons_deferrable_fk_tab (
  z,
  y,
  x varchar2(10),
  foreign key (z,y) references cons_deferrable_pk_tab deferrable
);

Now we insert data to the table will fails, because reference table has not been inserted data yet. 

insert into cons_deferrable_fk_tab values (1, 'one', 'foo');

ORA-02291: 整合性制約(ZOLO.SYS_C005382)に違反しました - 親キーがありません 

set constraints all deferred

And now the constraints are deferred, so we can insert data which still not inserted to the reference table. 

insert into cons_deferrable_fk_tab values (1, 'one', 'foo');

Though commit will fail. 

set constraints [Oracle SQL] 

Sql代码  

set constraint[s] all { immediate | deferred };
set constraint[s] constraint-name-1 [, constraint-name-n ...] { immediate | deferred };