Re: Am I supposed to be all scared of compound primary keys?

From: Chris Browne <cbbrowne(at)acm(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Am I supposed to be all scared of compound primary keys?
Date: 2010-05-03 15:15:29
Message-ID: 87zl0ht3se.fsf@ca.afilias.info
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

mike(at)kitchenpc(dot)com (Mike Christensen) writes:
> I have a table that stores a user ID and a subscription type, and this is
> really all it needs to store and any pair of values will always be unique.  In
> fact, I think this pair should be the primary key on the table.  However, I'm
> using Castle ActiveRecord which says at:
>
> http://www.castleproject.org/activerecord/documentation/v1rc1/usersguide/
> pks.html#CompositePK
>
> And I quote:
>
> Quick Note: Composite keys are highly discouraged. Use only when you have no
> other alternative.
>
> I get the feeling they're discouraged from a SQL point of view, but it doesn't
> actually say why anywhere.  Is there any good reason to avoid using composite
> keys on a table?  Why waste the space of an extra key if you don't have to? 
> Thanks!

They're discouraging it from an "interacting with our particular
object/relational mapping" perspective.

They have a fair bit of explanation on that web page, which seems to
point at composite keys being something they found was, within their
particular framework, more complex to support than "unnatural keys."

(They characterize composite keys as "natural," so presumably kludging
in a non-composite key is the "unnatural" thing :-).)

There are a number of "ORMs" which have a tough time coping with
composite keys, but that's an ORM problem, not an SQL problem.
--
It is considered artful to append many messages on a subject, leaving
only the most inflammatory lines from each, and reply to all in one
swift blow. The choice of lines to support your argument can make or
break your case. -- from the Symbolics Guidelines for Sending Mail

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Justin Pasher 2010-05-03 15:49:54 Latest source RPMs for 8.1.20
Previous Message Tom Lane 2010-05-03 13:56:25 Re: User defined type - analyze problem