Re: all foreign key should have index?

In message <m3irs1hfsw.fsf_at_mobile.int.cbbrowne.com>, Christopher Browne <cbbrowne_at_acm.org> writes
>> Is it necessary that all foreign key should have index?
>
>It is not necessary from a theoretical standpoint.
>
>It is necessary, from a practical standpoint, if the table pointed to
>is fairly large, and you want to be able to validate the foreign key
>references in a reasonable period of time.
>
>> SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE = 'R'
>> AND INDEX_NAME IS NOT NULL
>>
>> Above query does not return any row in my database. Is it bad?
>
>I don't know. Did you create a table in your database called
>"user_constraints?" That's not one of the standard view names in the
>SQL-92 INFORMATION_SCHEMA schema, so it's certainly not something that
>could be considered "standard."

Talking about Oracle by the look of it - in which case that query always returns nothing, the INDEX_NAME column in USER_CONSTRAINTS is used only to identify the index used to enforce a primary or unique key.

If there is no index on the foreign key column(s) in the child table, then Oracle will lock the entire child table for a delete or primary key update on the parent table - so you often, but not always, want the index, apart from the performance issue referred to above.

To check if a suitable index exists you will have to look in USER_IND_COLUMNS.

-- 
Eric Junkermann