Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!news.tele.dk!news.tele.dk!small.news.tele.dk!multikabel.net!feed20.multikabel.net!newsfeed.freenet.de!news.code-werk.net!open-news-network.org!sumatra.thomas-huehn.de!w3bhost.de!ilaria.aioe.org!aioe.org!not-for-mail
From: "x" <x@not-exists.org>
Newsgroups: comp.databases.theory
Subject: Re: all foreign key should have index?
Date: Thu, 2 Feb 2006 11:13:06 +0200
Organization: Aioe.org NNTP Server
Lines: 45
Message-ID: <drsif6$7tk$1@domitilla.aioe.org>
References: <1138629101.268470.215840@o13g2000cwo.googlegroups.com> <1138687059.234890.22280@g47g2000cwa.googlegroups.com> <drnsls$snq$1$8300dec7@news.demon.co.uk> <m364o0fgnh.fsf@mobile.int.cbbrowne.com> <dro97b$gn9$1$830fa7a5@news.demon.co.uk> <gep5EuDoD+3DFwKO@deptj.demon.co.uk> <1gu1u1htssqdv7kp2q5p3e3a2nf38fhqh3@4ax.com> <xn0ehzvhthnyl000@news-south.connect.com.au> <to82u15bdghg59qlphhvjokcbtpvvej9ss@4ax.com>
NNTP-Posting-Host: s9btCjTiVvvsgWfav5q8uQ.user.domitilla.aioe.org
X-Complaints-To: abuse@aioe.org
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Priority: 3
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MSMail-Priority: Normal
Xref: dp-news.maxwell.syr.edu comp.databases.theory:35743


"Gene Wirchenko" <genew@ucantrade.com.NOTHERE> wrote in message
news:to82u15bdghg59qlphhvjokcbtpvvej9ss@4ax.com...
> On Wed, 1 Feb 2006 20:17:11 +0000 (UTC), "Murdoc"
> <murdoc_0@hotmail.com> wrote:
>
> >Gene Wirchenko wrote:
> >
> >> On Tue, 31 Jan 2006 22:12:56 +0000, Eric Junkermann
> >> <eric@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. :-)