Обсуждение: Foreign keys and indexes
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
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 þ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
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
"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: >> 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. > > >
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