How to choose my primary key?

Question

I found this reading material on choosing a primary key.

• Is there a guide / blog post on how to choose the primary key for a given table?
• Should I use a auto-incremented/generated key, or should I base the primary key on the data being modeled (assuming it has a truly unique field)?
• Should the primary key always be long for performance's sake, or can I take an external unique id as primary key, even if it's a string?

Answer 1

I believe that in practice using a natural key is rarely better than a surrogate key.

The following are the main disadvantages of using a natural key as the primary key:

• You might have an incorrect key value, or you may simply want to rename a key value. To edit it, you would have to update all the tables that would be using it as a foreign key.

• It is often difficult to have a truly unique natural key.

• Natural keys are often strings. An index on an numeric field will be much more compact than one on a string field.

There is no hard rule on what the data type of the primary key should be. A numeric key normally performs better, but you could use a string, especially if the table is not big, and the tables that reference it are not big either.

Answer 2

A key is a set of attributes with two fundamental features: uniqueness and minimality. Minimality means the key has only the minimum number of attributes required to ensure uniqueness.

There are three criteria commonly applied as a guide to choosing a good key:

• Familiarity - keys should be meaningful and familiar to the people who use them
• Simplicity - keys should be as simple and concise as possible
• Stability - key values should change infrequently

These are good guidelines but are not absolute requirements. In all cases functional requirements and the needs of data integrity should determine what keys to use.