Primary key practice

Question

DROP TABLE IF EXISTS `media_publications`;
CREATE TABLE `media_publications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `media_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `description` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY (`id`,`user_id`)
) ENGINE=INNODB;

Would it be pointless to have id as a primary key here? Should I just have id + user_id as the primary key considering I need it as a foreign key to another table?

Uhm, why just `id` is not enough for FK in that another table? — zerkms, Jan 10 '12 at 10:26
Does the combination `media_id` and `user_id` uniquely identify a row? — Oded, Jan 10 '12 at 10:28
With InnoDB engine, you can almost never go wrong about setting the PK as auto_increment field. — N.B., Jan 10 '12 at 10:37
@zerkms Isn't it better to have an index on foreign keys references? (in my referring table votes.user_id + votes.id is a UNIQUE KEY so in fact it's a requirement to make the FK) So I'm wondering if I'll be needing primary key = id in addition to the compound key I proposed. — el_pup_le, Jan 10 '12 at 10:55
@zerkms Isn't it better to have an index on foreign keys references? (in my referring table votes.user_id + votes.media_publication_id is a UNIQUE KEY so in fact it's a requirement to make the FK) So I'm wondering if I'll be needing primary key = id in addition to the compound key I proposed. — el_pup_le, Jan 10 '12 at 11:01
You should choose your one and only innodb clustered index per table wisely - http://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601 — Jon Black, Jan 10 '12 at 11:12
@amiawizard: and why `id` is not enough to maintain relation? Why do you need to add `user_id` also there? — zerkms, Jan 10 '12 at 19:20
@zerkms ah good point :) I only add user_id for unique constraint, had constraint and index mixed up. thanks. — el_pup_le, Jan 11 '12 at 04:43

score 2 · Accepted Answer · answered Jan 10 '12 at 10:28

2

You must have id as the primary key because it is an AUTO_INCREMENT and you're using InnoDB. You can create a unique index on the 2 columns (I'd put user_id first) to use in child table foreign keys

answered Jan 10 '12 at 10:28

gbn

422,506
82
585
676

@amiawizard: so it's useful for queries on user_id by itself – gbn Jan 10 '12 at 11:15

score 0 · Answer 2 · answered Jan 10 '12 at 10:28

0

not each unique combination of rows should be a primary key. potentially you can use it this way, but it will be an awkward solution.

answered Jan 10 '12 at 10:28

Orentet

2,353
1
17
28

newtover · Answer 3 · 2012-01-10T10:49:51.793

It is a good idea to explicitly mark the primary key as the primary key.

That way you won't have to add another index that will partially duplicate you primary key or the foreign key to the users table (though the latter is not currently there).

P.S. many people seem to confuse identity fields with primary keys. The identity field can easily be a part of the primary key (but should be the prefix if the table is InnoDB and the field is autoincremented integer).

score 0 · Answer 4 · answered Jan 22 '12 at 12:21

It's pointless to have key on id, user_id since the primary key is always the last key in the secondary indexes in InnoDB (although optimizer not always takes the advantage of this), and since key may be used only if the left-most part is specified. So either create key only on user_id, or if it should be unique accross user_id/id, on user_id, id. In that way you can use index in both queries WHERE id = and WHERE user_id =

Primary key practice

4 Answers4