Re: RI

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.

regards, tom lane