0

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
9
  • Why is a "fast increase" of a primary key a potential problem? Also see Performance of multi field primary key or contrived key
    – LowlyDBA - John M
    Nov 13, 2015 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 - John M
    Nov 13, 2015 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, 2015 at 16:31
  • 1
    @GaryOlsson yes
    – dezso
    Nov 13, 2015 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, 2018 at 7:07

0

Reset to default

Browse other questions tagged or ask your own question.