What's the best practice for primary keys in t

Question

When designing tables, I've developed a habit of having one column that is unique and that I make the primary key. This is achieved in three ways depending on requirements:

1. Identity integer column that auto increments.
2. Unique identifier (GUID)
3. A short character(x) or integer (or other relatively small numeric type) column that can serve as a row identifier column

Number 3 would be used for fairly small lookup, mostly read tables that might have a unique static length string code, or a numeric value such as a year or other number.

For the most part, all other tables will either have an auto-incrementing integer or unique identifier primary key.

The Question :-)

I have recently started working with databases that have no consistent row identifier and primary keys are currently clustered across various columns. Some examples:

• datetime/character
• datetime/integer
• datetime/varchar
• char/nvarchar/nvarchar

Is there a valid case for this? I would have always defined an identity or unique identifier column for these cases.

In addition there are many tables without primary keys at all. What are the valid reasons, if any, for this?

I'm trying to understand why tables were designed as they were, and it appears to be a big mess to me, but maybe there were good reasons for it.

A third question to sort of help me decipher the answers: In cases where multiple columns are used to comprise the compound primary key, is there a specific advantage to this method vs. a surrogate/artificial key? I'm thinking mostly in regards to performance, maintenance, administration, etc.?

Answer 1

A natural key, if available, is usually best. So, if datetime/char uniquely identifies the row and both parts are meaningful to the row, that's great.

If just the datetime is meaningful, and the char is just tacked on to make it unique, then you might as well just go with an identify field.

Answer 2

I look for natural primary keys and use them where I can.

If no natural keys can be found, I prefer a GUID to a INT++ because SQL Server use trees, and it is bad to always add keys to the end in trees.

On tables that are many-to-many couplings I use a compound primary key of the foreign keys.

Because I'm lucky enough to use SQL Server I can study execution plans and statistics with the profiler and the query analyzer and find out how my keys are performing very easily.

Answer 3

GUIDs can be used as a primary key, but you need to create the right type of GUID so that it performs well.

You need to generate COMB GUIDs. A good article about it and performance statistics is The Cost of GUIDs as Primary Keys.

Also some code on building COMB GUIDs in SQL is in Uniqueidentifier vs identity(archive).

Answer 4

I avoid using natural keys for one simple reason -- human error. Although natural unique identifiers are often available (SSN, VIN, Account Number, etc.), they require a human to enter them correctly. If you're using SSNs as a primary key, someone transposes a couple of numbers during data entry, and the error isn't discovered immediately, then you're faced with changing your primary key.

My primary keys are all handled by the database program in the background and the user is never aware of them.

Answer 5

Natural versus artificial keys to me is a matter of how much of the business logic you want in your database. Social Security number (SSN) is a great example.

"Each client in my database will, and must, have an SSN." Bam, done, make it the primary key and be done with it. Just remember when your business rule changes you're burned.

I don't like natural keys myself, due to my experience with changing business rules. But if your sure it won't change, it might prevent a few critical joins.

Answer 6

I suspect Steven A. Lowe's rolled up newspaper therapy is required for the designer of the original data structure.

As an aside, GUIDs as a primary key can be a performance hog. I wouldn't recommend it.