Database Reference
In-Depth Information
Integrity Constraints and Transactions
It is interesting to note exactly when integrity constraints are checked. By default, integrity constraints are checked
after the entire SQL statement has been processed. There are also deferrable constraints that permit the validation
of integrity constraints to be postponed until either the application requests they be validated by issuing a
SET CONSTRAINTS ALL IMMEDIATE
command or upon issuing a
COMMIT
.
IMMEDIATE Constraints
For the first part of this discussion, we'll assume that constraints are in
IMMEDIATE
mode, which is the norm. In this
case, the integrity constraints are checked immediately after the entire SQL statement has been processed. Note that
I used the term “SQL statement,” not just “statement.” If I have many SQL statements in a PL/SQL stored procedure,
each SQL statement will have its integrity constraints validated immediately after its individual execution, not after
the stored procedure completes.
So, why are constraints validated
after
the SQL statement executes? Why not
during
? This is because it is very
natural for a single statement to make individual rows in a table momentarily inconsistent. Taking a look at the partial
work by a statement would result in Oracle rejecting the results, even if the end result would be OK. For example,
suppose we have a table like this:
EODA@ORA12CR1> create table t ( x int unique );
Table created.
EODA@ORA12CR1> insert into t values ( 1 );
1 row created.
EODA@ORA12CR1> insert into t values ( 2 );
1 row created.
EODA@ORA12CR1> commit;
Commit complete.
And we want to execute a multiple-row
UPDATE
:
EODA@ORA12CR1> update t set x=x-1;
2 rows updated.
If Oracle checked the constraint after each row was updated, on any given day we would stand a 50-50 chance
of having the
UPDATE
fail. The rows in
T
are accessed in
some
order, and if Oracle updated the
X=1
row first, we would
momentarily have a duplicate value for
X
and it would reject the
UPDATE
. Since Oracle waits patiently until the end of
the statement, the statement succeeds because by the time it is done, there are no duplicates.
DEFERRABLE Constraints and Cascading Updates
Starting with Oracle 8.0, we also have the ability to
defer
constraint checking, which can be quite advantageous for
various operations. The one that immediately jumps to mind is the requirement to cascade an
UPDATE
of a primary key
to the child keys. Many people claim you should never need to do this—that primary keys are immutable (I am one
of those people), but many others persist in their desire to have a cascading
UPDATE
. Deferrable constraints make this
possible.
