0

This question already has an answer here:

I'm interested to know in which case I should create a primary key on multiple columns and why ?

To be more specific about my question, I want to know what are the advantages and disadvantages of primary key on multiple columns in terms of performance.

I did some research and found this question where someone is saying

Another good question is SHOULD a primary key be multiple columns. :) – Sonny Boy Nov 17 '09 at 17:31

Well, that's a different question, and the answer is NO, NOT EVER, AND IF YOU DO I WILL HUNT YOU DOWN AND HURT YOU. Just a personal preference, of course :-) – Stu Dec 4 '09 at 16:46

Despite the joke, there should be some good reason on why one shouldn't use primary key on multiple columns but I couldn't find any answer.

I was wondering about this because I have a concrete problem.
Consider the following table:

CREATE TABLE IF NOT EXISTS `user_travel_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_user` int(10) unsigned NOT NULL,
  `day` date NOT NULL,
  `origin` varchar(8) NOT NULL,
  `destination` varchar(8) NOT NULL,
  `data` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ukey` (`id_user`, `day`, `origin`, `destination`)
)

In this table, for each user I have some data for each day and for each travel from origin to destination.

I need this table because it's very easy to use WHERE clause on it. However, the numbers of line in this table are just insane. Usually, a user has ~50 origins and ~100 destinations. With 30 days filled & 200 users, there's ~30 million lines in my table. Because each user has around ~150000 lines, I had to create some tools for them to edit/delete/add lines in group.
And, because of these tools, users tends to delete old lines and create a whole bunch of new lines. This leads to a very fast increase of my primary key.
While it is not a problem now, it could be one pretty soon.

So, since I don't use the id on this table, I was wondering if I could do something like :

PRIMARY KEY (`id_user`, `day`, `origin`, `destination`)

This answer is also quite useful but didn't help with my problem

|improve this question

marked as duplicate by mustaccio, Max Vernon, RolandoMySQLDBA, Paul White♦ Nov 14 '15 at 4:35

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Why is a "fast increase" of a primary key a potential problem? Also see Performance of multi field primary key or contrived key – LowlyDBA Nov 13 '15 at 15:41
  • 1
    Do you really plan on having more than 9,223,372,036,854,775,807 rows in your table, even accounting for frequent deletes? There's also a UNIQUEIDENTIFIER as an option if so. – LowlyDBA Nov 13 '15 at 16:12
  • 2
    To be honest, those two SO questions are only mildly useful. My take: if you don't plan to join other tables to this one (on the primary key), just use the natural key. If you do, use a surrogate key (bigint if int is not enough). – dezso Nov 13 '15 at 16:31
  • 1
    @GaryOlsson yes – dezso Nov 13 '15 at 16:48
  • 1
    Generally, an associative or bridging table is a good candidate for a compound primary key. For example, if multiple books have multiple authors, you can have a bridging table with only 2 columns: bookid and authorid. By designating both columns as a primary key, you guarantee uniqueness of the entries (that is, you never mention a book/author combination more than once). – Manngo Mar 18 '18 at 7:07

Browse other questions tagged or ask your own question.