oracle constraints(3)

oracle constraints 应用
oracle constraints可以设置为立即检查或者当时事务提交时检查。
    可以在创建约束的时候指定是deferrable。然后通过set constraints xxx set deferred或者immediate,也可以在seesion级别设定所有约束为deferred或者immediate(alter seesion set constraints deferred/immediate)。
   
   SQL> select t.constraint_name, t.status, t.validated, t.deferrable from user_constraints t;

CONSTRAINT_NAME STATUS VALIDATED DEFERRABLE
------------------------------ -------- ------------- --------------
SYS_C003765 ENABLED VALIDATED NOT DEFERRABLE
SYS_C003768 ENABLED NOT VALIDATED NOT DEFERRABLE
UK_T ENABLED NOT VALIDATED NOT DEFERRABLE

SQL> alter table t2 add primary key(vid) deferrable;

Table altered

SQL>
SQL> select t.constraint_name, t.status, t.validated, t.deferrable from user_constraints t;

CONSTRAINT_NAME STATUS VALIDATED DEFERRABLE
------------------------------ -------- ------------- --------------
SYS_C003765 ENABLED VALIDATED NOT DEFERRABLE
SYS_C003772 ENABLED VALIDATED DEFERRABLE
SYS_C003768 ENABLED NOT VALIDATED NOT DEFERRABLE
UK_T ENABLED NOT VALIDATED NOT DEFERRABLE

SQL> select * from t2;

VID VNAME VSEX
---------- ---------- ----------
1 a y
2 b
3 c x

SQL> insert into t2 values (1,'d','y');

insert into t2 values (1,'d','y')

ORA-00001: 违反唯一约束条件 (PORTALDB.SYS_C003772)

SQL> set constraints SYS_C003772 deferred;

Constraints set

SQL> insert into t2 values (1,'d','y');

1 row inserted

SQL> set constraints SYS_C003772 deferred;

Constraints set

SQL> commit;

commit

ORA-02091: 事务处理已重算
ORA-00001: 违反唯一约束条件 (PORTALDB