Re: all foreign key should have index?

"Gene Wirchenko" <genew_at_ucantrade.com.NOTHERE> wrote in message news:to82u15bdghg59qlphhvjokcbtpvvej9ss_at_4ax.com...
> On Wed, 1 Feb 2006 20:17:11 +0000 (UTC), "Murdoc"
> <murdoc_0_at_hotmail.com> wrote:
>
> >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?
>
> >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).

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

That depends on how you define an index. :-) Received on Thu Feb 02 2006 - 10:13:06 CET