From: Michael Fuhr <mike@fuhr.org>
To: T E Schmitz <mailreg@numerixtechnology.de>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Join Table
Date: Mon, 1 Nov 2004 10:03:24 -0700
Message-ID: <20041101170324.GA18943@winnie.fuhr.org> (raw)
In-Reply-To: <41866598.5040909@numerixtechnology.de>
References: <41866598.5040909@numerixtechnology.de>
On Mon, Nov 01, 2004 at 04:34:32PM +0000, T E Schmitz wrote:
>
> I have created the following join table: the two FKs are the PK of the
> table. Typically, I will need to select rows for a given ITEM_FK.
>
> Question: is it necessary/advisable to create an index for the ITEM_FK
> column? Or is this redundantbecause this column is already one of the PK
> columns?
Here's an excerpt from the documentation for CREATE TABLE:
"PostgreSQL automatically creates an index for each unique constraint
and primary key constraint to enforce the uniqueness. Thus, it is
not necessary to create an explicit index for primary key columns."
However, read the "Multicolumn Indexes" section in the "Indexes"
chapter to be sure you understand when the index will be used and
when it won't be:
http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html> CREATE TABLE SUPPLY
> (
> ITEM_FK integer NOT NULL,
> CONTACT_FK integer NOT NULL,
> COST numeric (7,2),
> PRIMARY KEY (ITEM_FK,CONTACT_FK)
> );
If ITEM_FK and CONTACT_FK are foreign keys, then you might want to
add foreign key constraints to ensure referential integrity.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, mailreg@numerixtechnology.de
Subject: Re: Join Table
In-Reply-To: <20041101170324.GA18943@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox