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

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 2

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.