Transactions - Oracle Database Transactions and Locking Revealed - page 89

EODA@ORA12CR1> insert into parent values ( 1 );
1 row created.
EODA@ORA12CR1> insert into child values ( 1 );
1 row created.
We have a parent table, PARENT , and a child table, CHILD . Table CHILD references table PARENT , and the constraint
used to enforce that rule is called CHILD_FK_PARENT (child foreign key to parent). This constraint was created as
DEFERRABLE , but it is set to INITIALLY IMMEDIATE . This means we can defer that constraint until COMMIT or to some
other time. By default, however, it will be validated at the statement level. This is the most common use of the
deferrable constraints. Most existing applications won't check for constraint violations on a COMMIT statement, and it
is best not to surprise them with that. As defined, table CHILD behaves in the fashion tables always have, but it gives us
the ability to explicitly change its behavior. Now let's try some DML on the tables and see what happens:
EODA@ORA12CR1> update parent set pk = 2;
update parent set pk = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (EODA.CHILD_FK_PARENT) violated - child record found
Since the constraint is in IMMEDIATE mode, this UPDATE fails. We'll change the mode and try again:
EODA@ORA12CR1> set constraint child_fk_parent deferred;
Constraint set.
EODA@ORA12CR1> update parent set pk = 2;
1 row updated.
Now it succeeds. For illustration purposes, I'll show how to check a deferred constraint explicitly before
committing, to see if the modifications we made are in agreement with the business rules (in other words, to check
that the constraint isn't currently being violated). It's a good idea to do this before committing or releasing control to
some other part of the program (which may not be expecting the deferred constraints):
EODA@ORA12CR1> set constraint child_fk_parent immediate;
set constraint child_fk_parent immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (EODA.CHILD_FK_PARENT) violated - parent key not found
It fails and returns an error immediately as expected, since we knew that the constraint had been violated. The
UPDATE to PARENT was not rolled back (that would violate the statement-level atomicity); it is still outstanding. Also
note that our transaction is still working with the CHILD_FK_PARENT constraint deferred because the SET CONSTRAINT
command failed. Let's continue now by cascading the UPDATE to CHILD :
EODA@ORA12CR1> update child set fk = 2;
1 row updated.
EODA@ORA12CR1> set constraint child_fk_parent immediate;
Constraint set.
EODA@ORA12CR1> commit;
Commit complete.