Re: PK/FK and locks

Create an index on your foreign key table on the columns that are part of the foreign key.

See this document from Thomas Kyte.
http://osi.oracle.com/~tkyte/unindex/index.html

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
"Gary Knopp" <gknopp_at_ascend.com> wrote in message
news:8g19gf$skr_at_nntpa.cb.lucent.com...


>

>

> I create a Primary/Foreign key relationship between


> two tables.

>

>      T1

>  ===========

>  key(PK)  catg

>  ---            -----

>   1           Cars

>   2           Sports

>   3           Places

>

>

>      T2

>  ============

>  key(FK)  Values

>  -------       -------

>   1             Toyota

>   1             Camry

>   2             Baseball

>

>

>  + If UserA makes any change to table T2 then

>    userB cannot delete any records in T1 until

>    UserA commits changes.  This seems to be

>    true whether the FK in T2 has been modified

>    or not.

>

>    In the above example if UserA updates record field

>   'Camry' to 'FireBird' in table T2, UserB can't delete

>   T1 record 'Places'   until UserA commits, even though

>   no T2 record   references this key.  Why?

>

>    If the above is true, how can I inform the user that a delete

>    cannot be performed on T1 since changes have not

>    been committed on T2.  I don't want the user

>    sitting in a WAIT state forever.

>

>    Do I have to do a 'select for update' on every table

>    that has a FK constraint on this table?

>

>

>

>

>

>

>

>