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

Re: RI

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: RI
Date: 2010-06-24 11:39:45
Message-ID: 20100624133945.4727eb43@anubis.defcon1 (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

  • Re: RI at 2010-06-24 04:48:18 from Tom Lane

pgsql-novice by date

Next:From: Atif JungDate: 2010-06-24 12:05:20
Subject: sqlca structure
Previous:From: Jayadevan MDate: 2010-06-24 10:04:35
Subject: Re: DROPPING INDEX error

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