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

Re: Foreign keys

From: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Foreign keys
Date: 2004-08-27 20:16:04
Message-ID: 1093637766.12274.45.camel@retsol1 (view raw or flat)
Thread:
Lists: pgsql-novice
Thanks all,
The data types are correct (we use serial/integer fields for the prime
and foreign key references).
Where I had gone wrong was in assuming that the slave foreign key
declaration created an index on that column on the slave table (it seems
to  in Informix - where I'm converting from) - I have modified my table
create scripts to create the indexes explicitly. Conversion  is looking
good so far!

On Fri, 2004-08-27 at 20:13, Tom Lane wrote:

    Jeremy Semeiks <jrs(at)farviolet(dot)com> writes:
    > On Fri, Aug 27, 2004 at 09:31:51AM +0100, Steve Tucknott wrote:
    >> Am I right in assuming that a foreign key on a table does not explicity
    >> create an index on that column on the foreign table?
    
    > Adding the foreign key won't add the index itself, but I believe that
    > foreign keys can only be declared on columns declared unique.
    
    Right, the referenced column must have an index.  However, the system
    does not require the referencING column to have an index.  This can be a
    performance loss --- in particular when deleting records from the
    referencED table, because the FK machinery then has to seqscan to see
    if there are any matching referencING rows.  If your master table is
    pretty stable, though, you may not care enough to pay the costs of
    keeping an index on the slave table.
    
    You can also get burnt if the referenced and referencing columns aren't
    of the exact same datatype --- again, not enforced by the system, but a
    good way to shoot yourself in the foot performance-wise.
    
    			regards, tom lane
    
    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
          joining column's datatypes do not match



Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

In response to

pgsql-novice by date

Next:From: Betsy BarkerDate: 2004-08-27 20:18:34
Subject: Re: pgsql functions and transactions?
Previous:From: Tom LaneDate: 2004-08-27 19:34:13
Subject: Re: pgsql functions and transactions?

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