sql - 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.