Re: Foreign key constraints

A copy of this was sent to "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> (if that email address didn't require changing) On Fri, 18 Jun 1999 14:58:27 +0100, you wrote:

>The technically correct answer is
>that there is no requirement for an
>index on the child table.
>
>The locking problem referred to in
>your book is that in the absence of
>an index on the CHILD table, the
>entire PARENT table will be locked
>if you attempt to update or delete
>a PARENT row. (This is a necessity
>for maintaining referential integrity).

you got that backwards. The child table is locked (we are going to full scan it anyhow)

<quote>
No Index on the Foreign Key

Figure 5-4 illustrates the locking mechanisms used by Oracle when no index is defined on the foreign key and when rows are being updated or deleted in the parent table. Inserts into the parent table do not require any locks on the child table.

Notice that a share lock of the entire child table is required until the transaction containing the DELETE statement for the parent table is committed. If the foreign key specifies ON DELETE CASCADE, then the DELETE statement results in a table-level share-subexclusive lock on the child table. A share lock of the entire child table is also required for an UPDATE statement on the parent table that affects any columns referenced by the child table. Share locks allow reading only; therefore, no INSERT, UPDATE, or DELETE statements can be issued on the child table until the transaction containing the UPDATE or DELETE is committed. Queries are allowed on the child table. </quote>

In 7.0 and 7.1, a table lock on the PARENT resulted but for different circumstances. In 7.0 and 7.1 the case was:

<quote>
Notice that a share lock (reading allowed, writing not allowed) of the entire parent table is required until the transaction containing the INSERT statement for the child table is committed. Therefore, no INSERT, UPDATE, or DELETE statements can be issued on the parent table until this transaction is committed. Queries are allowed on the parent table. </quote>

>However, if the PARENT table is locked,
>it is then impossible to insert or rows
>in the child table.
>
>The upshot of this is that in an OLTP
>system where the parent table is
>subject to updates or deletes it is
>necessary to have an index on the
>child table for performance (concurrency)
>reasons
>
>
>P.S. This is the case in 7.3 and 8.0, I
>hadn't thought to check that 8.1.5 does
>anything differently.
>
>See pages 9-10 on in the 8.0 application
>developers guide.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 18 1999 - 09:38:53 CDT