一个constraint如果被定义成deferrable那么这个constraints可以在deferred和imediate两种状态相互转换。
deferred只在transaction中有效，也就是只可以在transaction过程中使constraint失效，但如果transaction commit的话，transaction会变成immediate。
1* create table cons_parent (id number(10),name varchar2(10))
SQL> /
Table created.
SQL> create table cons_child (id number(10),name varchar2(10));
Table created.
1* alter table cons_parent add primary key (id)
SQL> /
Table altered.
alter table cons_child add constraints chi_fk_par foreign key (id)
references cons_parent(id)
SQL> alter table cons_child add constraints chi_fk_par foreign key (id)
2 references cons_parent(id)
3 /
Table altered.
一个constraints默认是NOT DEFERRABLE的。
1 select constraint_name||‘ ‘||deferrable from all_constraints
2* where constraint_name=‘CHI_FK_PAR‘
SQL> /
CONSTRAINT_NAME||‘‘||DEFERRABLE
---------------------------------------------
CHI_FK_PAR NOT DEFERRABLE
NOT DEFERRABLE的不能在deferred和imediate两种状态相互转换
SQL> set constraints chi_fk_par deferred;
SET constraints chi_fk_par deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable
--解决办法：删除该约束后，并重建deferrable约束。
SQL> alter table cons_child drop constraints chi_fk_par;
Table altered.
1 alter table cons_child add constraints chi_fk_par foreign key (id)
2* references cons_parent(id) deferrable
SQL> /
Table altered.
1 select constraint_name||‘ ‘||deferrable from all_constraints
2* where constraint_name=‘CHI_FK_PAR‘
SQL> /
CONSTRAINT_NAME||‘‘||DEFERRABLE
---------------------------------------------
CHI_FK_PAR DEFERRABLE
一个constraint如果被定义成deferrable那么这个constraints可以在deferred和imediate两种状态相互转换
SQL> set constraints chi_fk_par immediate;
Constraint set.
1* insert into cons_child values (2,‘llll‘)
SQL> /
insert into cons_child values (2,‘llll‘)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found
SQL> set constraints chi_fk_par deferred;
Constraint set.
SQL> insert into cons_child values (2,‘llll‘);
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found
deferrable会影响CBO的计划，并且正常情况下没有应用的必要，所以建议不要修改，而用系统默认的non deferrable
本篇文章摘自：关于表约束constraint \\constraints 三个注意的地方
ORA-02447: cannot defer a constraint that is not deferrable,布布扣,bubuko.com
ORA-02447: cannot defer a constraint that is not deferrable
原文：http://www.cnblogs.com/yabingshi/p/3852830.html