[20170512]延迟约束问题.txt

--//别人问的问题,通过例子来说明:

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//建立约束时可以deferrable initially immediate 或者 deferrable initially deferred.
--//还有一种NOT DEFERRABLE,这种跟deferrable initially immediate存在什么区别.

create table t
( a int constraint check_a check ( a > 0 ) deferrable initially immediate,
  b int constraint check_b check ( b > 0 ) deferrable initially deferred,
  c int constraint check_c check ( c > 0 ) deferrable,
  d int constraint check_d check ( d > 0 ) );

SCOTT@book> select constraint_name,deferrable,deferred from user_constraints  where table_name='T';
CONSTRAINT_NAME DEFERRABLE     DEFERRED
--------------- -------------- ---------
CHECK_A         DEFERRABLE     IMMEDIATE
CHECK_B         DEFERRABLE     DEFERRED
CHECK_C         DEFERRABLE     IMMEDIATE
CHECK_D         NOT DEFERRABLE IMMEDIATE

--//实际上其他都很好理解.就是对于字段A,D.两者情况都是立即起作用.也就是你不能这样插入数据,或者修改数据.

SCOTT@book> insert into t values (-1,2,3,4);
insert into t values (-1,2,3,4)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_A) violated

SCOTT@book> insert into t values (1,2,3,-4);
insert into t values (1,2,3,-4)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_D) violated

--//都是在dml时马上起作用.视乎两者没有什么区别.

2.实际上oracle还可以临时设置set constraints all deferred;或者set constraints all immediate;

SCOTT@book> set constraints all deferred;
Constraint set.

SCOTT@book> insert into t values (1,2,3,-4);
insert into t values (1,2,3,-4)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_D) violated

SCOTT@book> insert into t values (-1,2,3,4);
1 row created.

SCOTT@book> insert into t values (1,-2,3,4);
1 row created.

SCOTT@book> insert into t values (1,2,-3,4);
1 row created.

--//这样就可以看出两者的区别,设置set constraints all deferred;时仅仅对约束是DEFERRABLE有效.而对于约束NOT DEFERRABLE无效.
--//当然提交时不满足约束,肯定报错.

SCOTT@book> commit ;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SCOTT.CHECK_A) violated

--//临时修改set constraints all immediate;
SCOTT@book> set constraints all immediate;
Constraint set.

SCOTT@book> insert into t values (-1,2,3,4);
insert into t values (-1,2,3,4)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_A) violated

SCOTT@book> insert into t values (1,-2,3,4);
insert into t values (1,-2,3,4)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_B) violated

--//字段B插入负数也马上报错.