Re: problem with foreign key

In article <3bc2c2f8_at_news.iprimus.com.au>, "Howard says...
>
>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.
>

well, not so fast. (i despise rules of thumb as they seem to become laws and they are only correct x% of the time)

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:292016138754

There are cases where not having the index is JUST dandy.

If you never update the parents primary key (and why would you) or never delete from the parent -- this never happens. In this case, they are deleting -- so it is called for.

Say I have a table of

create table lookup_zip_to_state ( zip_code number primary key,

                                   state varchar2(2) );

and I have many child tables of this lookup. Since the lookup table is virtually imutable (updates so rare), I would not suggest you need to index the fkeys -- maintainance of this table is done during off hours. Also, we would not update this table and zip codes are not deleted hence the maintanence of this table consists of inserts. I might be able to save myself from having to create MANY indexes on many large tables in this case.

(i read your referenced paper. the issue is somewhat alleviated in 9i, not resolved. The lock happens for a shorter duration but still has the ramifications. Try this in 9i

create table p ( x int primary key );
create table c ( y references p );

insert into p values ( 1 );
insert into p values ( 2 );
commit;

Now, in one session: insert into c values (1);

In another             delete from p where x = 2;
In a third             insert into c values (1);

see what happens. So, in 9i I would say the need is still there)

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

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp