Re: Foreign key constraints

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). 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.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Kenneth C Stahl wrote in message <376A4072.D3DA4BB8_at_lucent.com>...
>Question: If a foreign key constraint is created for a column in a
>table, is it required that there be an index on that column? One book I
>read suggested that if there isn't an index for the column then there
>may be locking problems when updates occur.
>
>Ken
>
Received on Fri Jun 18 1999 - 08:58:27 CDT