Transactions - Expert Oracle Database Architecture - page 285

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.
And that's the way it works. Note that to defer a constraint, you must create it that way—you have to drop and re-create
the constraint to change it from nondeferrable to deferrable. That might lead you to believe that you should create all
of your constraints as “deferrable initially immediate,” just in case you wanted to defer them at some point. In general,
that is not true. You want to allow constraints to be deferred only if you have a real need to do so. By creating deferred
constraints, you introduce differences in the physical implementation (in the structure of your data) that might not be
obvious. For example, if you create a deferrable UNIQUE or PRIMARY KEY constraint, the index that Oracle creates to support
the enforcement of that constraint will be a non-unique index. Normally, you expect a unique index to enforce a unique
constraint, but since you have specified that the constraint could temporarily be ignored, it can't use that unique index.
Other subtle changes will be observed, for example, with NOT NULL constraints. In Chapter 11, we'll see how an index on a
NOT NULL column can be used in many cases where a similar index on a NULL column can't be. If you allow your NOT NULL
constraints to be deferrable, the optimizer will start treating the column as if it supports NULLs —because it in fact does
support NULLs during your transaction. For example, suppose you have a table with the following columns and data:
EODA@ORA12CR1> create table t
2 ( x int constraint x_not_null not null deferrable,
3 y int constraint y_not_null not null,
4 z varchar2(30)
5 );
Table created.