CODEWITHSUNDEEP
sqlmysqlprimary-key
maximus
maximus

Reputation: 53

MySQL table PRIMARY KEY 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)
);

Upvotes: 1

Views: 283

Answers (2)

JustBoo
JustBoo

Reputation: 1741

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

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332521

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.

Upvotes: 2

Related Questions