Re: RI

Le Thu, 24 Jun 2010 00:48:18 -0400,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> a écrit :

> Jasen Betts <jasen(at)xnet(dot)co(dot)nz> writes:
> > On 2010-06-24, Jean-Yves F. Barbier <12ukwn(at)gmail(dot)com> wrote:
> >> does a Referential Integrity toward a table also acts like an index, or
> >> am I obliged to create this index?
>
> > The RI does not create any indices.
> > There is no requirement to create an index.
> > In most cases creating an index at one or both ends or the reference
> > is a good idea.
>
> Well, it's a little bit more complicated than that. A foreign key
> constraint can only be created when the referenced (primary key) column
> has a unique or primary key constraint. In Postgres, a unique/PK
> constraint always has an associated index. So you're already guaranteed
> an index on that end of the FK. What will not be present, unless you
> create it, is an index on the referencing column. It often is a good
> idea to create that index too, but there are some cases where such an
> index isn't worth its maintenance overhead. You will want such an index
> if you often change or delete entries in the referenced column. If you
> seldom do that, and don't often issue queries on the referencing column,
> then maybe you don't need that index.

This is crystal clear :)
Thanks Tom

JY
--
Sure, and of course I would vote for a woman for president!
Quite naturally, we wouldn't have to pay her so much.