Thread: Am I supposed to be all scared of compound primary keys?
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:
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!
Mike
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!
Mike
On Sat, May 1, 2010 at 8:25 PM, Mike Christensen <mike@kitchenpc.com> wrote: > 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! From reading that, they're discouraged from a hibernate point of view. I've never had a problem with composite keys in SQL myself.
On May 1, 2010, at 7:25 PM, Mike Christensen wrote:
> \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!
A composite key is generally better than creating a surrogate key just
so you have a single-column key. It's possible that the note is
referring to systems that handle composite keys poorly (PostgreSQL
handles them just fine), or are concerned about ORMs which don't
support them at all (like Django's) or support them badly.
--
-- Christophe Pettus
xof@thebuild.com
On Sat, 2010-05-01 at 19:25 -0700, Mike Christensen wrote:
I'm not familiar with this project.. That said it seems they have some automated SQL updating/insert/relation building going on in the classes. Nothing more than simplifying the class initialising of .net ADO record sets which are overly complicated.
It seems the class automation can not work with composite keys directly to build relation between classes; create SQL commands to up the records, and make sure within castle framework the composite key has been updated to all the other classes having relations.
This warning has no impact on any database but a limitation and warning about Castle-project framework itself.
The database does not care either way. Given this limitation is within the framework i follow the advice and not use a composite key.
All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.
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!
I'm not familiar with this project.. That said it seems they have some automated SQL updating/insert/relation building going on in the classes. Nothing more than simplifying the class initialising of .net ADO record sets which are overly complicated.
It seems the class automation can not work with composite keys directly to build relation between classes; create SQL commands to up the records, and make sure within castle framework the composite key has been updated to all the other classes having relations.
This warning has no impact on any database but a limitation and warning about Castle-project framework itself.
The database does not care either way. Given this limitation is within the framework i follow the advice and not use a composite key.
All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.
mike@kitchenpc.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