ORACLE约束的属性(二)
Deferred,immediate.
www.2cto.com
2)这对属性是在创建的constraint的时候定义的,不能被修改.
notice:如果建立了Deferrable的uk或pk,只会建立相应的nonuniquce index,而不会建立uniquce index
SQL> alter table empx drop constraint fk_deptx;
SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable;
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';
[plain]
CONSTRAINT_NAME COND STATUS DEFERRABLE DEFERRED VALIDATED
------------------------ ---------------- ------------------------ ---------------- ---------------- ------------
PK_EMPX ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
FK_DEPTX ENABLED DEFERRABLE IMMEDIATE VALIDATED
SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50);
insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found
在会话级设置：
SQL> set constraint FK_DEPTX deferred;
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';
[plain]
CONSTRAINT_NAME COND STATUS DEFERRABLE DEFERRED VALIDATED
---------------- ---------------- ------------------------ ---------------- --------------------------- ----------------
PK_EMPX ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
FK_DEPTX ENABLED DEFERRABLE IMMEDIATE VALIDATED
SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50);
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found
单独deferrable的含义就是是否允许约束检查延后进行。单独设置deferrable为deferrable之后，约束检查延后是不可以直接使用的，要配合deferred参数，如果该参数是immediate，那么约束还是在DML的时候进行应用。如果deferred参数设置为deferred，约束就是在事务commit提交的时候应用，出现错误就连带回滚rollback整个事务。
2.Deferred,immediate(default value)
www.2cto.com
deferred属性是配合deferrable属性使用的。当deferrable设置为deferrable之后，可以通过set constraints在会话级进行deferred属性的设置，来确定约束应用时点。
定义约束是使用initially关键字来确定约束的deferred属性取值。
SQL> alter table empx drop constraint fk_deptx;
SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred;
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';
[plain]
CONSTRAINT_NAME COND STATUS DEFERRABLE DEFERRED VALIDATED
---------------- ---------------- ------------------------ ---------------- --------------------------- ----------------
PK_EMPX ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
FK_DEPTX ENABLED DEFERRABLE DEFERRED VALIDATED
SQL> insert into empx (empno,ename,job,mgr,hiredate,sal