Oracle-L: Foreign Keys and Table Locking

From: Sam Bootsma <SamB_at_cpas.com>
Date: Tue, 30 Jul 2002 14:58:38 -0800
Message-ID: <F001.004A6B4B.20020730145838@fatcity.com>

Hello List,

Here is my question:
If a composite foreign key (defined using on delete cascade) is partially indexed, will a delete operation on the referenced table use the index on the partial foreign key?

More Detailed Explanation of Question
Assume the following

1. A master table with columns A, B, C, and D.
2. A detail table with columns A, B, C, and X.  
3. The detail table has a foreign key on columns A, B, and C, that reference

the same columns on the master table.
4. The foreign key on the detail table is defined using "on delete cascade".

If a delete or update operation is performed on the master table, the operation is cascaded to the detail table. If there is no index on the detail table, Oracle will lock the entire table. If there is an index on columns A,B, and C of the detail table, Oracle will not lock the detail table, but will use the index.

My question (repeated): If there is an index on columns A and B of the detail table (but NOT column C), will Oracle lock the detail table? Or will Oracle use the existing index?

I appreciate any help anybody can provide. I am on digest mode only, so if possible, please send a reply to both my individual email and to the list. That way I get a response much quicker.

Thanks!

Sam Bootsma, OCP
Technical Support Analyst
CPAS Systems Inc.
samb_at_cpas.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Sam Bootsma
INET: SamB_at_cpas.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jul 30 2002 - 17:58:38 CDT