Re: Join Table

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