Date: Mon, 1 Nov 2004 10:03:24 -0700
From: Michael Fuhr <mike@fuhr.org>
To: T E Schmitz <mailreg@numerixtechnology.de>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Join Table
Message-ID: <20041101170324.GA18943@winnie.fuhr.org>
References: <41866598.5040909@numerixtechnology.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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/