Deferred Constraints
Normally, constraints are checked as data is
inserted or updated in the row. When a row is inserted, all the
constraints are checked and the row either accepted or
rejected. You can, however, defer the constraint checking until
the transaction
commits. In that case, the
row is inserted into the table and constraints are not checked
until the COMMIT
is issued. If the row then
fails, it is removed from the table. You can change the status
of the constraints by setting them as DEFERRED or
IMMEDIATE. In order to change the constraint, it must be
deferrable.
SQL>
set constraint active_ck deferred;
set constraint active_ck deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable
Here, the active_ck constraint was
not created as deferrable. In fact, none of the constraints we
have created so far can be DEFFERED. Let’s recreate the
job_fk as a deferrable
constraint.
SQL>
alter table emp drop constraint job_fk;
Table
altered.
SQL>
alter table emp
2 add (constraint job_fk foreign key (job_key)
3 references job (job_key)
4 on delete cascade deferrable);
Table
altered.
SQL>
set constraint job_fk deferred;
Constraint set.
SQL>
set constraint job_fk immediate;
Constraint set.
First, I drop the current constraint and
recreate it with the deferrable key work. Now, I can defer the
constraint and reset it back to IMMEDIATE.
This seems a bit complicated, so why would you want to defer a
constraint? One example is with foreign key constraints. You
can, and should, set up foreign key constraints to insure that
the data in your tables is correctly related. Once the foreign
keys are in place, you must insure that data is inserted/updated
to the parent first, then to the child tables.
With a number of foreign key constraints in
place, you could run into the situation where you cannot get the
data into the tables without violating at least one foreign
key. If I could just get the data loaded, then the check the
foreign keys the data would be accepted. Here is where the
deferred constraint is useful. By deferring the foreign key
constraints, you can get the data into the tables and then
validate it against the foreign keys. Invalid data is still
rejected.
