From: T E Schmitz <mailreg@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Cc: Michael Fuhr <mike@fuhr.org>
Cc: tgl@sss.pgh.pa.us
Cc: dev@archonet.com
Subject: Re: Join Table
Date: Mon, 01 Nov 2004 18:12:02 +0000
Message-ID: <41867C72.4060001@numerixtechnology.de> (raw)
In-Reply-To: <20041101170324.GA18943@winnie.fuhr.org>
References: <41866598.5040909@numerixtechnology.de>
<20041101170324.GA18943@winnie.fuhr.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mailreg@numerixtechnology.de, mike@fuhr.org, tgl@sss.pgh.pa.us, dev@archonet.com
Subject: Re: Join Table
In-Reply-To: <41867C72.4060001@numerixtechnology.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox