Re: Foreign keys

Jeremy Semeiks <jrs@farviolet.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