Re: all foreign key should have index?

Gene Wirchenko wrote:

> On Tue, 31 Jan 2006 22:12:56 +0000, Eric Junkermann
> <eric_at_deptj.demon.co.uk> 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?
>
> Sincerely,
>
> Gene Wirchenko

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 value?".

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).

--