16

My current database design makes use of a multiple column primary key to use existing data (that would be unique anyway) instead of creating an additional column assigning each entry an arbitrary key. I know that this is allowed, but was wondering if this is a practice that I might want to use cautiously and possibly avoid (much like goto in C).

So what are some of the disadvantages I might see in this approach or reasons I might want a single column key?

3
  • 2
    I dunno, I think this would have been better on SO. Feb 7, 2011 at 20:06
  • 2
    @FrustratedWithFormsDesigner It could go to SO, but I think it works here, too, since the focus of the question seems to be on the "what are pros and cons of this approach" rather than "how do I do X?".
    – Adam Lear
    Feb 7, 2011 at 20:10
  • @Anna Lear♦: It's a "pros and cons" about design decisions that will have a direct and definite impact on coding, so I think SO would be a better place. Feb 7, 2011 at 20:44

9 Answers 9

9

Usually when you have a table with a multi-column primary key, it's the result of a join table (many-to-many) that became elevated to be its own entity (and thus deserves it's own primary key). There are many who would argue that any join table SHOULD be an entity by default, but that's a discussion for another day.

Let's look at a hypothetical many to many relationship:

Student * --- * Class

(a Student can be in multiple classes, a Class can have multiple students).

In between those two tables will be a junction table called StudentClass (or ClassStudent depending how you write it). Sometimes, you want to keep track of stuff like when the student was in the class. So you'll add it to the StudentClass table. At this point, StudentClass has become a unique entity...and should be given a name to recognize it as such e.g. Enrollment.

Student 1 --- * Enrollment * --- 1 Class

(a student can have many Enrollments, each Enrollment is for one class (or going the opposite way a Class can have many Enrollments, each Enrollment is for one Student).

Now you can query things like, how many students were enrolled in the Chemistry 101 class this past year? Or what classes was the student John Doe enrolled in while attending Acme University? This was possible without the separate primary key, but once you have a primary key for enrollment an easier query would be of these enrollments (by id), how many students received a passing grade?

The determination of whether an entity deserves a PK boils down to how much querying (or manipulation) you will do for that entity. Let's say for instance, you wanted to attach the assignments completed for a student in a class. The logical place to attach this entity (Assignment) would be on the Enrollment entity. Giving enrollment it's own primary key would make the Assignment queries simpler.

1
  • 1
    So you'll add it to the StudentClass table. At this point, StudentClass has become a unique entity...and should be given a name to recognize it as such e.g. Enrollment. It's such a simple thing, but there's so much value in doing this!
    – macwier
    Feb 7, 2018 at 11:27
9

It makes sense having a separate id column. When you want to get something from your database table, it is easier to do:

SELECT whatever FROM table WHERE id=13

than SELECT whatever FROM table WHERE col1='val1' AND col2='val2' AND col3='val3'

For example, in a web application it translates to a url looking like this:

www.somewebsite.com/somepage.php?id=13

or like this:

www.somewebsite.com/somepage.php?col1=val1&col2=val2&col3=val3
6
  • 4
    And it's much easier to add a related table when you can link on an Id, instead of several columns
    – CaffGeek
    Feb 7, 2011 at 18:50
  • 3
    Sorry, at this point I have to -1, as A) it's not black and white. Adding an ID column comes with negatives like where and when do you generate that new ID. In addition, it could result in extra joins or SELECT queries. And, B), I don't have any idea how this actually causes any type of URL requirement (unless you are working with a bad framework). My URLs don't have any query strings with ?id=13 in them, let alone ?col1=val1&col2=val2&col3=val3.
    – Nicole
    Feb 7, 2011 at 19:19
  • 2
    @renesis: This site has unique questions and users, which are in the URLs. Although, this is somewhat of a special case, as that particular data does not change.
    – Michael K
    Feb 7, 2011 at 19:32
  • 1
    @Renesis, most (perhaps all) modern db's have auto_increment integer column types that can generate the ID's automatically and safely, and report them back via sql query or library function call. Or in a distributed environment, you use a large random hash. Some DB's will even make a hidden id column for you if you dont have one already in the table. Feb 7, 2011 at 19:34
  • @Michael - I didn't say IDs are never in URLs. Of course they are. If you have URLs that represent a row of data, then yeah, that data should probably have a unique ID. Unless some other part of the URL already provides the other parts of the multi-key. @GrandmasterB Neither of the last two companies I've worked for (over 6 years), which both use MySQL (one also supported Oracle and SQL Server) were able to use auto-increment, nor a large random hash.
    – Nicole
    Feb 7, 2011 at 20:04
9

Basically you're asking whether you should use surrogate or natural keys (in your case it sounds like composite natural keys). Here's a great article: http://www.agiledata.org/essays/keys.html

I prefer surrogate keys because they simplify administration over the life of the DB (you never have to worry about the implication of keys changing meaning, which should never happen but does in any real system where humans are involved). However, if there are a lot of "lookup" tables in the DB (i.e. tables that are basically key:value pairs), then surrogate keys can get cumbersome because you have to join those tables into the query in order to obtain meaningful results.

For example, let's say you have two entities: Address, and Country.

  • The relationship is: Address *-----1 Country
  • The Country entity is basically a key:value pair (e.g. US:United States, CA:Canada, MX:Mexico, etc...)
  • To query this structure for all Addresses in the US:

select * from Address where CountryCode = 'US'

  • To perform the same query with surrogate keys:

select Address.* from Address join Country on Address.CountryID = Country.ID where Country.Code = 'US'

I'm comfortable mandating natural keys for lookup tables and surrogate keys for everything else, if I'm pretty sure that that the natural keys won't be changing too often, if ever.

5

It depends on how you access the data. If you do a lot of partial-key lookups (where you select records based on say only two of the three keys) then you'll want to keep the multi-part keys. OTOH, if you have a lot of 1:1 relationships with other tables, it probably makes more sense to have a surrogate key.

1

I like to always have a surrogate primary key for each table. But there aren't many "hard" reasons to enforce this that I have heard.

The one time that I have ever had a multi-column natural key bite me was with ORM. Occasionally I would have issues with a multiple column primary key using Linq To Entities.

1

Never say never, but joining on 4 columns is a pain. The more columns you have with intelligent data, the better chance those values may change. Databases can be setup to maintain referencial integrity with cascading updates.

You can always create another index to handle the unique values.

Performance is probably negligible in most cases, but you can test your queries with and without the surragate key.

0

I find it hard to come up with a good reason to mandate a separate key, but like you said lots of folks put it in.

I don't find this of help (especially with storage) when dealing with fact/detail tables. Canonical example a sales fact table with a (customer_key,store_key,product_key) with quantity doesn't make much sense to have a record-level key.

0

Having PK be an autoincrement int reduces hassle if you find that your composite key can in actuality have duplicates.

0

There's a good discussion going back to 2002 on Ask Tom. It's Oracle-specific, but the wider discussion is relevant whatever database you're using.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.