Should I use multiple column primary keys or add a new column?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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

Answer 9

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.