Thread: Foreign keys and indexes

Foreign keys and indexes

From
Marc Compte
Date:
Dear list,

This might be too basic for a question but I just couldn't find the
answer so far.

Does PostgreSQL create an implicit index also for foreign keys? or must
I create it explicitly?

Thank you,

Marc Compte

Re: Foreign keys and indexes

From
"Filip Rembiałkowski"
Date:
2007/6/5, Marc Compte <mcompte@sigte.udg.es>:
> Dear list,
>
> This might be too basic for a question but I just couldn't find the
> answer so far.
>
> Does PostgreSQL create an implicit index also for foreign keys? or must
> I create it explicitly?

FK is just a constraint, you wil have to create indexes manually if
you need them.


--
Filip Rembiałkowski

Re: Foreign keys and indexes

From
Richard Broersma Jr
Date:
> Does PostgreSQL create an implicit index also for foreign keys? or must
> I create it explicitly?

No, you foreign keys are not automatically indexed.  They only way they would be is if the FK is
part of a composite unique or primary key.  So you will probably have to create your one indexes
on FKs.

Regards,
Richard Broersma Jr.


Re: Foreign keys and indexes

From
Ragnar
Date:
On þri, 2007-06-05 at 11:49 +0200, Marc Compte wrote:

> Does PostgreSQL create an implicit index also for foreign keys?

no


>  or must I create it explicitly?

if you want one, yes.

not everyone wants an index on all their foreign keys,
but they can be useful in some circumstances.

gnari



Re: Foreign keys and indexes

From
Michael Fuhr
Date:
On Tue, Jun 05, 2007 at 11:49:20AM +0200, Marc Compte wrote:
> Does PostgreSQL create an implicit index also for foreign keys? or must
> I create it explicitly?

PostgreSQL doesn't create an index on the referencing column(s) of
a foreign key constraint; if you want an index then you'll need to
create it yourself.

--
Michael Fuhr

Re: Foreign keys and indexes

From
"A. Kretschmer"
Date:
am  Tue, dem 05.06.2007, um 11:49:20 +0200 mailte Marc Compte folgendes:
> Dear list,
>
> This might be too basic for a question but I just couldn't find the
> answer so far.
>
> Does PostgreSQL create an implicit index also for foreign keys?

No, only for primary keys to enforce the uniqueness.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Foreign keys and indexes

From
Gregory Stark
Date:
"Marc Compte" <mcompte@sigte.udg.es> writes:

> Does PostgreSQL create an implicit index also for foreign keys? or must I
> create it explicitly?

It won't allow you to create a foreign key that points to a column without a
unique index on it.

postgres=# create table b (i integer references a(i));
ERROR:  there is no unique constraint matching given keys for referenced table "a"

However if you ever update or delete the referenced records then it also helps
performance to have an index on the referencing column which Postgres doesn't
enforce.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


Re: Foreign keys and indexes

From
Marc Compte
Date:
Thanks to everyone for the prompt reply :)

Good thing about answers is when they raise up new questiosn, so you can
keep on learning all the time.

This one answer, for instance, brings me another question. Does having a
composite primary mean the system will create an individual index on
each of the fields? or is the index created on the composition only?

For instance, in the implementation of a N:M relationship, declaring the
primary as (foreign1, foreign2) will create two indexes? or just one?

Thanks again

Marc Compte

En/na Richard Broersma Jr ha escrit:
>> Does PostgreSQL create an implicit index also for foreign keys? or must
>> I create it explicitly?
>>
>
> No, you foreign keys are not automatically indexed.  They only way they would be is if the FK is
> part of a composite unique or primary key.  So you will probably have to create your one indexes
> on FKs.
>
> Regards,
> Richard Broersma Jr.
>
>
>


Re: Foreign keys and indexes

From
Scott Marlowe
Date:
Marc Compte wrote:
> Thanks to everyone for the prompt reply :)
>
> Good thing about answers is when they raise up new questiosn, so you
> can keep on learning all the time.
>
> This one answer, for instance, brings me another question. Does having
> a composite primary mean the system will create an individual index on
> each of the fields? or is the index created on the composition only?
>
> For instance, in the implementation of a N:M relationship, declaring
> the primary as (foreign1, foreign2) will create two indexes? or just one?

Just one (and please don't top post.  :) )

Re: Foreign keys and indexes

From
Oliver Elphick
Date:
On Tue, 2007-06-05 at 17:07 +0200, Marc Compte wrote:
>
> For instance, in the implementation of a N:M relationship, declaring
> the
> primary as (foreign1, foreign2) will create two indexes? or just one?

Just one

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html