When Would You Choose NOT to Use an Identity/Autonumber Field as Your Primary Key?

Question

I don't believe in a Silver Bullet, but I really like to use sequences or autonumber identity columns as my primary key columns for database tables. They're unique, they index well, and I don't have to worry about null values.

On the other hand, in some cases, they seem redundant when there are other unique columns in the table that could serve the same purpose. Say, for example, you're building a table that maps 9-digit ZIP codes to city zones. The ZIP code field could work just as well (provided you can guarantee the data format and no duplication of values).

To the point: My experience, as it is with any of us, is limited. What other real-world examples have lead folks to choose not to use an autonumber column as the primary key for a table, and why?

This is a "broaden your horizons" type of thing for me, and I hope to learn a bit from folks who have worked with a plethora of databases and had compelling reasons to choose otherwise.

Answer 1

A link table springs to mind as the most obvious choice for a composite key

Answer 2

Really the only time I can think of to use an identity column is when the number of fields required to make a primary key is large, or if the field which is the primary key is really big (like a 20 character string). In all other instances, I prefer not to use them.

The problem no one every brings up about identities, is what happens when something happens to the data. Since the key is based solely on when the record was added, reloading data into a table after a catastrophic event is a real problem. Now the dbms should help you out and prevent someone from truncating a table, or switching the values of the primary keys...should. Things happen, tables get corrupted, or database updates run into problems. With identity primary keys, all of a sudden you're left with a mess trying to figure out which identity values goes with which row....wait except you can't, because the identity value has no meaning in regards to the data. With handful of entries, you might be ok, but when you start having larger tables of maybe a couple million values (ours was a little over 11 million when this happened) that gets to be really problematic in a hurry. Everyone says, "that's a worse case scenario, it'll never happen." That is until it does.

Answer 3

IMHO it is crucial to use a identity column as even the simplest table can become more important in the future.

The only time i wouldn't use one would be where i used a GUID instead, for circumstances where perhaps records were created on disconnected clients that then needed to synchronize with a central system.

Answer 4

I'm a firm believer in the use of technical primary keys almost without exception so my answer would have to be... never.

Answer 5

One area where I have not used an autonumber field is when defining a DateDimension table as part of a star schema. In this instance I used an integer representing the date in yyyymmdd format. This allowed for fast joins between the central fact table and the DateDimension (as an auto-number ID column also would). However ...

The DateDimension table contained other date representations (e.g. smalldatetime column, dayOfWeek column, etc). If users only wanted the date in yyyymmdd format the join wasn't necessary as the date dimension key in the central fact table already stored this information.

In general I'm not a big fan of keys containing business information. Typically, the assumption you make about the primary key when designing the schema won't hold true over time and you'll become unstuck. In this case I was fairly sure that the date would not!

Answer 6

my rule of thumb is: "if you're going to add records in normal usage, use an autoincrement PK; if it's a static table, use whatever identifier is more 'natural'"

IOW: users, history records, assets; all get an autoincrement PK. zip/city, type/descriptions, machine IDs, usually get a 'natural' key.

Answer 7

I will typically avoid auto_increment columns in situations where frequent data dumps / loads / merges are required and I have foreign key relationships. Attempting to merge data from two table instances of the same schema that use auto incrementing ids is a terrifying problem.

This sort of usage doesn't crop up for most, but my line of work involves a lot of batch processing where each batch then gets merged in to a master database for later analysis/use.

Answer 8

One exception to the principle expressed by Iain Hoult, Javier, and TK would be the use of an employee number or "badge number" as the PK of a personnel table. In this case, the PK can be called a "meaningful key" only because we have handed the employee the PK of his personnel record.

-Al.