Re: FOREIGN KEY constraint

In article <2pirep$e6c_at_dockmaster.phantom.com> ynp_at_phantom.com (Youri Podchosov) writes:

>Does anybody have anything to say about table/column level constraint
>FOREIGN KEY? Any warnings/observations/cautions? How does it compare to
>database triggers used for the same purpose?
 

>The reason for these questions is: we're gonna redefine (I mean ALTER)
>the most part of legacy database to enforce referential integrity by
>means of ORACLE itself, so although I personally don't see any reasonable
>objections to such a modification, not everybody is 100% sure about it.

Take note of the section entitled 'Concurrency Control, Indexes and Foreign Keys' in Chapter 6 of the Application Developers Guide. In particular note that if there is no index on the foreign key the **entire** parent table is locked when a record is inserted into the child. This lock is held until the transaction is committed.
If there is a index on the foreign key this problem does not occur.

Donald Munro (ccahdm_at_beluga.upe.ac.za) Computing Centre, University of Port Elizabeth Received on Thu Apr 28 1994 - 09:43:06 CEST