Re: problem with foreign key

Not quite true: a lock of sorts is still taken, but it is a different sort of lock when an index is present than when it is not -and the new lock type is a share lock, so DML on the child table is permitted.

The general rule is: every column that is declared to use a foreign key constraint ought to have an index slapped on it in double-quick time, otherwise complete, exclusive child table locking is the inevitable result.

For other foreign key constraints, check out http://www.geocities.com/howardjr2000 and look at the "tips" page.

Regards
HJR "Peter Laursen" <pl_at_mail1.remove.this.stofanet.dk> wrote in message news:3bc1d6cd$0$11605$ba624c82_at_nntp01.dk.telia.net...
>
> "Rüdiger J. Schulz" <johannes.schulz_at_web.de> wrote in
> message news:3bc1aa64$1_at_netnews.web.de...
> > hi all,
> >
> >
> <SNIP tables>
> > transaction 1: delete a row in the parent table *without*
> a commit
> > transaction 2: wants to insert a row in the CHILD-table
> > *problem*: transaction 2 wait until transaction 1 send a
> commit!!!
>
> > is it true, that oracle lock the whole child-table, while
> deleting a parent-
> > key?
>
> Yes it is true that oracle will lock the table when there is
> NO INDEX on the foreign key.
> create an index on child(pid) and no locks will be taken.
>
> See http://govt.us.oracle.com/~tkyte/ Unindexed Foreign
> Keys
> or
> http://technet.oracle.com/doc/server.815/a68003/01_05dta.htm
> #1574
>
> /Peter
>
Received on Tue Oct 09 2001 - 02:25:23 CDT