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

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

If a constraint is defined as deferrable, the constraints can switch between deferred and imediate states.

Deferred is valid only in transaction, that is, it can only invalidate the constraint in the transaction process. However, if transaction commit is used, transaction will become 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.

A constraints is not deferrable by default.

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 cannot switch between deferred and imediate states.

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

-- Solution: Delete the constraint and recreate the deferrable constraint.

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

If a constraint is defined as deferrable, the constraints can switch between deferred and imediate states.

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 will affect the CBO plan, and there is no need for applications under normal circumstances, so we recommend that you do not modify it, instead, use the default non deferrable

This article is excerpted from three notes about table constraints constraint \ constraints.