Database Reference
In-Depth Information
■
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.
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
