| |
 |
|
Oracle Tips by Burleson |
OCP Instructors Guide for
Oracle DBA Certification
Chapter 8 -
Miscellaneous Oracle Information
Unindexed Foreign Keys
and Locking Problems
-
When rows are deleted from the parent table, Oracle will search for
foreign key values in the dependent table that match the deleted
rows’ primary key or unique key values. How Oracle handles parent
table row deletes depends upon how the delete clause was specified
during foreign key creation. Oracle will either set matching foreign
key values in the dependent table to NULL, restrict deletion if rows
having matching foreign key values are found or delete rows in the
dependent table that have matching foreign key values.
-
When the parent table’s primary key or unique
key is updated, Oracle will search the dependent table for foreign
key values that match the value of the primary key or unique key
being updated. If any matching values are found, Oracle will prevent
the primary key or unique key from being updated.
The locking problem occurs when Oracle performs
the foreign key search during parent table row deletions and primary
key/unique key updates. If the foreign key is indexed, Oracle will
use the index to perform the foreign key value search. The index
columns must match the foreign key columns in order and in number
(no additional columns can be contained in the index).
The above text is
an excerpt from:
OCP Instructors Guide for Oracle DBA Certification
A Study Guide to Advanced Oracle Certified Professional Database
Administration Techniques
ISBN 0-9744355-3-8
by Christopher T. Foot
http://www.rampant-books.com/book_2003_2_OCP_print.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
