Re: Foreign key constraints

From: <markp7832_at_my-deja.com>
Date: Fri, 18 Jun 1999 14:14:25 GMT
Message-ID: <7kdk7o$iad$1@nnrp1.deja.com>

In article <376A4072.D3DA4BB8_at_lucent.com>,   Kenneth C Stahl <kstahl_at_lucent.com> wrote:
> 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
>

Note - I wrote a reply to this, but when I hit send I ended up on the DejaNews login screen. If this appears twice my apologies to the group

Ken, When a DML statement updates a referenced table then all rows that reference the FK column(s) must be locked pending completion of the activity and application of the delete rule. If there is no index on the referencing table for the restrained column then a table level lock is used. If the referenced table has heavy update and delete activity this can impact updaters to the referencing table as the material you read states. But if the referenced table has little to no update activity or the referencing table has no update activity then you can get away without having an index to support the FK. Here is SQL that will find all FK without indexes to support them that will work with versions 7.3 and 8.0:

set echo off
rem
rem SQL*Plus script to find unindexed Foreign Keys rem
rem 1998 11 17 Mark D Powell Modify to disp FK constraint and

rem                            idx name; re-arrange output

rem 1998 07 24 Thomas Kyle Oracle Newsgroup post rem
set pagesize 60

column columns         format a20 word_wrapped
column table_name      format a30
column constraint_name format a30 fold_before
column index_name      format a30 fold_before

spool constraints_unindexed

select     a.table_name,
           decode( b.table_name, NULL, '****', 'OK' ) Status,
           a.constraint_name, a.columns,
           b.index_name, b.columns

from
( select a.table_name, a.constraint_name,

             max(decode(position, 1,      column_name,NULL)) ||
             max(decode(position, 2,', '||column_name,NULL)) ||
             max(decode(position, 3,', '||column_name,NULL)) ||
             max(decode(position, 4,', '||column_name,NULL)) ||
             max(decode(position, 5,', '||column_name,NULL)) ||
             max(decode(position, 6,', '||column_name,NULL)) ||
             max(decode(position, 7,', '||column_name,NULL)) ||
             max(decode(position, 8,', '||column_name,NULL)) ||
             max(decode(position, 9,', '||column_name,NULL)) ||
             max(decode(position,10,', '||column_name,NULL)) ||
             max(decode(position,11,', '||column_name,NULL)) ||
             max(decode(position,12,', '||column_name,NULL)) ||
             max(decode(position,13,', '||column_name,NULL)) ||
             max(decode(position,14,', '||column_name,NULL)) ||
             max(decode(position,15,', '||column_name,NULL)) ||
             max(decode(position,16,', '||column_name,NULL)) columns
    from sys.dba_cons_columns a, sys.dba_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'

   group by a.table_name, a.constraint_name ) a, ( select table_name, index_name,

             max(decode(column_position, 1,      column_name,NULL)) ||
             max(decode(column_position, 2,', '||column_name,NULL)) ||
             max(decode(column_position, 3,', '||column_name,NULL)) ||
             max(decode(column_position, 4,', '||column_name,NULL)) ||
             max(decode(column_position, 5,', '||column_name,NULL)) ||
             max(decode(column_position, 6,', '||column_name,NULL)) ||
             max(decode(column_position, 7,', '||column_name,NULL)) ||
             max(decode(column_position, 8,', '||column_name,NULL)) ||
             max(decode(column_position, 9,', '||column_name,NULL)) ||
             max(decode(column_position,10,', '||column_name,NULL)) ||
             max(decode(column_position,11,', '||column_name,NULL)) ||
             max(decode(column_position,12,', '||column_name,NULL)) ||
             max(decode(column_position,13,', '||column_name,NULL)) ||
             max(decode(column_position,14,', '||column_name,NULL)) ||
             max(decode(column_position,15,', '||column_name,NULL)) ||
             max(decode(column_position,16,', '||column_name,NULL))

columns

    from sys.dba_ind_columns
   group by table_name, index_name ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%' /
spool off

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jun 18 1999 - 09:14:25 CDT