Transactions - Oracle Database Transactions and Locking Revealed - page 88

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.

■ it is considered an extremely bad practice to perform update cascades to modify a primary key. it violates the
intent of the primary key. if you have to do it once to correct bad information, that's one thing, but if you find you are
constantly doing it as part of your application, you will want to go back and rethink that process—you have chosen the
wrong attributes to be the key!

Note

In early releases of Oracle, it was possible to do a CASCADE UPDATE , but doing so involved a tremendous
amount of work and had certain limitations. With deferrable constraints, it becomes almost trivial. The code could
look like this:

EODA@ORA12CR1> create table parent
2 ( pk int primary key )
3 /
Table created.

EODA@ORA12CR1> create table child
2 ( fk constraint child_fk_parent
3 references parent(pk)
4 deferrable
5 initially immediate
6 )
7 /
Table created.