Re: FOREIGN KEY constraint

In article <1012_at_wtc34a.DaytonOH.NCR.COM>, mbernier_at_wtc34a.DaytonOH.NCR.COM (Mike Bernier) writes:
>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?
>>
>
>Youri,
>
>When using declarative referential integrity, you have to be aware of how
>Oracle locks the parent child table. If the foreign key in the child table is
>indexed, a row level lock is applied on the appropriate parent row but if the
>foreign key is not indexed, the COMPLETE PARENT TABLE is LOCKED, essentially
>prohibiting parent table updates until any child table updates have been
>committed or rolled back. Please reference pages 6-9 through 6-11 in th

Not quite. If the foreign key in the child table is indexed, only the primary key INDEX ENTRY of the referenced parent table row is locked, not the parent row itself, and so updates to the parent row can take place (and complete/commit), even though there is a pending insert/update in the child table (in another session) that references the parent row. In this scenario, the only update on the master table that is blocked is an update of the referenced parent row's primary key column(s), which you shouldn't really be changing anyway!

If the foreign key in the child table isn't indexed, then ORACLE takes out a share lock on the referenced master table. This still allows row locks to be acquired on the master rows, but, as you say, blocks any updates, inserts or deletes.

>
>Mike Bernier

Tony Rothwell                                      rothwelt_at_heac004.gb.ec.ps.net