Re: Join Table

On Mon, Nov 01, 2004 at 06:12:02PM +0000, T E Schmitz wrote:

> 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.

If you want to use an index scan when querying by the 2nd column
alone then you'd need to create an index on it. Queries using the
1st column alone or the 1st column with the 2nd column will use the
primary key index. You can use EXPLAIN ANALYZE to see which index,
if any, the planner uses.

> 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?

Here's an excerpt from PostgreSQL's CREATE TABLE documentation:

Technically, PRIMARY KEY is merely a combination of UNIQUE and
NOT NULL, but identifying a set of columns as primary key also
provides metadata about the design of the schema, as a primary
key implies that other tables may rely on this set of columns as
a unique identifier for rows.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/