Skip site navigation (1) Skip section navigation (2)

Re: RI

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: RI
Date: 2010-06-24 04:48:18
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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

In response to

  • Re: RI at 2010-06-24 03:59:56 from Jasen Betts


  • Re: RI at 2010-06-24 05:00:39 from Mladen Gogala
  • Re: RI at 2010-06-24 11:39:45 from Jean-Yves F. Barbier

pgsql-novice by date

Next:From: Mladen GogalaDate: 2010-06-24 05:00:39
Subject: Re: RI
Previous:From: Jasen BettsDate: 2010-06-24 03:59:56
Subject: Re: RI

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group