Primary Keys - Autonumber or Unique Field Value (1 Viewer)

Cosmos75

Registered User.
Local time
Yesterday, 20:39
Joined
Apr 22, 2002
Messages
1,281
Is it better to use an autonumber as a primary key OR to use a field in a table that will alway be unique? I read an article by Luke Chung (President of FMS), that the key field for a table should be meaningless and numeric and that he recommends using an Autonumber field as the key for "most" tables.

Should I always use an autonumber then?
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:39
Joined
Feb 19, 2002
Messages
42,981
The operative word here is "most". If you ALREADY have a unique identifier that originates in another system such as PartNo or StudentID, my recommendation is to use that identifier as the primary key. I don't believe in arbitrarily adding autonumbers to every table for the sole purpose of serving as the primary key. I ONLY use autonumbers when I don't already have a unique identifier OR it would take too many fields in combination to ensure uniqueness. For example, you could not reasonably use a person's name as a uniqueID so if you are creating a NEW application and your organization does not already have a person table defined, you'll need to use an autonumber as a primary key in order to ensure uniqueness.

If you do use an autonumber primary key despite the fact that your table already contains a unique candidate key, make sure that you create a unique index on the candidate key so your business rules can be enforced.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:39
Joined
Feb 28, 2001
Messages
27,001
I concur with Pat, and in fact strongly believe that it is worth going a little bit out of your way to use meaningful primary keys WHEN IT IS REASONABLE TO DO SO.

Things that can reasonably be primary keys include

Employee ID number within a company.

Computer's serial number combined with brand-name.

Customer ID number used for mail-out autosort.

Don't go out of your way if you have to force it. In that case, use the autonumber.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:39
Joined
Feb 19, 2002
Messages
42,981
I visit the FMS site periodically to read the new articles and have recommended them many times to posters in this forum. The article you are referring to is no exception. It is well written and provides sound advice. The_Doc_Man and I are not disagreeing with what Luke said, only expanding on the "most" reference. There is a time to use a "natural" key and a time to use an artificial one. All of us would strongly recommend against your creating some meaningful key if you had no existing "natural" key. Many people seem to want to build composite keys by taking things like the year portion of date and the first three letters of the last name and the phase of the moon followed by a sequence number to avoid duplicates. That type of key should be avoided at all costs. And if the "natural" key you are getting from another system is built that way, you should consider replacing it with an autonumber as the primary key for your tables and keeping the old number only for compatibility.
 

Cosmos75

Registered User.
Local time
Yesterday, 20:39
Joined
Apr 22, 2002
Messages
1,281
Natural vs Meaningful key

What is the difference between a "natural" key and a "meaningful" key?

Say for example I am keeping track of paint usage? The name of the paint is always unique so doesn't that make it a "natural" key? Would it also be a "meaningful" key? Or is it not a meaningful key because it only uniquely identifies the paint?
 

David R

I know a few things...
Local time
Yesterday, 20:39
Joined
Oct 23, 2001
Messages
2,633
Well...

The name of the paint may be unique, now, but what if you change the formulation? This goes back to our little organic chemistry discussion of a month or so ago; if you need to know what the formulation was in 1999 and you've upped the Toluene content since then, do you a) rename the paint, or b) make the PK include both the Name and the Year. At that point you're starting to get into 'stretched' meanings of meaningful...if you get my meaning.:D

A meaningful PK that would probably never change would be a SNN, or a Invoice#.
 

Cosmos75

Registered User.
Local time
Yesterday, 20:39
Joined
Apr 22, 2002
Messages
1,281
Dazed and confused...

Just when I thought I was getting the hang of table stucture design...:confused:
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:39
Joined
Feb 28, 2001
Messages
27,001
A natural key vs. a meaningful key, eh?

In this discussion, don't assume that either key has to be "prime" to be involved here.

"Natural" keys are keys that sort of "leap out" at you as "Of course that's a good key." For instance, the Library of Congress book number is a PERFECTLY natural key if you run a bookstore that only carries books registered in the Library of Congress. But it has no particular meaning unless you look up the book in your database.

The Dewey Decimal System for libraries also would be a natural providing you could keep the numbers consistent. In this case, the key would also have meaning because the numbers of the Dewey system convey categories. So the Dewey system would be both "natural" AND "meaningful" for a book key.

"Meaningful" keys are keys that when you look at them, you know immediately what they mean. One could imagine a key of astronomical information based on a body's formal designator. So we would recognize Rigel, Arcturus, Bootes, Betelgeuse, Sirius, Antares, etc. Astronomers would recognize Tau Ceti, Alpha Centauri, Beta Orionis, Gamma Draconis, etc.

Yeah, I know, long names aren't always good keys for efficiency reasons - but they would be good examples of MEANINGFUL keys.
 

Users who are viewing this thread

Top Bottom