Foreign keys and indexes

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

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.

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

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

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

"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

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:

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. :) )

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