ORA-02447: cannot defer a constraint that is not deferrable，ora-02447defer

ORA-02447: cannot defer a constraint that is not deferrable，ora-02447defer

一個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 三個注意的地方