Re: all foreign key should have index?

From: Gene Wirchenko <>
Date: Wed, 01 Feb 2006 13:08:35 -0800
Message-ID: <>

On Wed, 1 Feb 2006 20:17:11 +0000 (UTC), "Murdoc" <> wrote:

>Gene Wirchenko wrote:
>> On Tue, 31 Jan 2006 22:12:56 +0000, Eric Junkermann
>> <> wrote:
>> [snip]
>> > When you delete a parent row, or update its key, the DBMS needs to find
>> > the children, either to cascade the operation or to forbid it - how can
>> > it do this efficiently without an index? But of course if you never do
>> > those things,
>> > you might still need it to find child rows efficiently anyway.
>> Why does it have to be an index?

>Generally: efficiency. If you want the DB to enforce referential integrity, the operation of doing
>so needs to be efficient. A search of the entire 'FK' table to ensure that a record can be deleted
>from the 'PK' table is (a) inefficient; and (b) pointless. Or even worse, on a cascade delete. The
>search (when using an index) really comes down to "Is there an entry in the index for this field
>Again, it does come down to the size of the table. A table with a maximum of about 10 rows is not
>going to have a large performance gain with an index (or maybe a performance detriment).

     That is not the point. Why does it have to be an INDEX?

     You have been assuming that an index is the only way. It is not.


Gene Wirchenko Received on Wed Feb 01 2006 - 22:08:35 CET

Original text of this message