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
