Message-ID: <41867C72.4060001@numerixtechnology.de>
Date: Mon, 01 Nov 2004 18:12:02 +0000
From: T E Schmitz <mailreg@numerixtechnology.de>
Reply-To: mailreg@numerixtechnology.de
User-Agent: Mozilla Thunderbird 0.7 (Windows/20040616)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: Michael Fuhr <mike@fuhr.org>, tgl@sss.pgh.pa.us, dev@archonet.com
Subject: Re: Join Table
References: <41866598.5040909@numerixtechnology.de>
	<20041101170324.GA18943@winnie.fuhr.org>
In-Reply-To: <20041101170324.GA18943@winnie.fuhr.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hello Mike/Tom/Richard,
Thank you for your replies.

Michael Fuhr wrote:

> On Mon, Nov 01, 2004 at 04:34:32PM +0000, T E Schmitz wrote:
> 
>>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?
> 
> 
> 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

I see. If using a multi-column PK, the order matters.
So, if I want to access the table both via the 1st and 2nd PK column, I 
would have to define an index for the 2nd column to avoid a full table scan.

Let's ask the question the other way round: I remember seeing a 
discussion (re Oracle) whether to use a multi-column PK or a unique 
constraint in such a situation - I got the impression it is one of these 
"religious" discussions ;-).
What are the pros and cons?

I have a few join tables. In the example I gave earlier the rows might 
be updated and selected via either of the two FKs. I have other join 
tables the rows of which will never be updated.
In any case, the FK/FK combination has to be unique.

If using a multi-column unique constraint, I presume the order matters 
just as it does with multi-column PKs?


> If ITEM_FK and CONTACT_FK are foreign keys, then you might want to
> add foreign key constraints to ensure referential integrity.

Thank you for the tip. I had done that further down in my init file via 
an ALTER TABLE...ADD CONSTRAINT.

-- 


Regards/Gruß,

Tarlika Elisabeth Schmitz