MySQL table PRIMARY KEY question?

Question

I' was wondering should my primary key look like this PRIMARY KEY (id, category_id, posts_id) or look like this PRIMARY KEY (id)?

Here is my MySQL table.

CREATE TABLE posts_categories (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
category_id INT UNSIGNED NOT NULL,
posts_id INT UNSIGNED NOT NULL,
date_created DATETIME NOT NULL,
PRIMARY KEY (id, category_id, posts_id),
UNIQUE KEY (category_id, posts_id)
);

Answer 1

I recommend using:

PRIMARY KEY (category_id, posts_id)

The id value will always be unique - what won't be, is the paring of category_id and posts_id.

But I missed that you already have a unique key defined on the category_id and posts_id columns, so you're primary key could be just the id. But the primary key means that it will be a clustered index - you'll be searching for these two columns more than you would be the id column so searches should improve minutely over a non-clustered index on the two columns.

Answer 2

The multiple column key is called a Composite Key or a Compound Key

As stated they are completely valid and have benefits. See links. :-)