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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
