When to use Composite Primary Key?
Posted by: Dae Hwang
Date: March 14, 2006 08:11AM


I am building a forum application. A simplified schema follows:

id int unsigned not null,
user_id int unsigned not null,
subject varchar(50) not null,
created_at datetime not null,

CREATE TABLE topic_replies (
topic_id int unsigned not null,
id int unsigned not null auto_increment,
user_id int unsigned not null,
message text not null,
PRIMARY KEY(topic_id, id));

As you can see, I am using composite primary key for `topic_replies` table. I think it's natural for each topic reply to be identified by its topic's id plus its own sub-id. Well, at least I thought that's why we have composite primary key support in the first place.

However, some ORM libraries like Rail's ActiveRecord don't support composite primary keys. I was quite suprised to find out that its developers are very against the use of composite primary keys. They claim that there is no benefit in using composite primary keys.

So I'm turning to MySQL community. Is there really no benefit of using composite primary keys? What's pros and cons of using them? I will appreciate any comments!


Dae San Hwang

Options: ReplyQuote

Written By
When to use Composite Primary Key?
March 14, 2006 08:11AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.