[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插入负数也马上报错.