databasedev.co.uk - database solutions and downloads for microsoft access

Primary Key Fields

Primary Keys - A Natural Key vs. a Meaningful Key:

By definition, a relational database must contain normalized tables, and to be properly normalized, a table must contain a primary key. Database developers often disagree about whether it's better to use naturally occurring data or meaningless values as a table's primary key. Technically, there is no right or wrong to this debate - only very strong opinions.

Primary key Defined

A primary key uniquely identifies each record within a table, but that's only half the story. The main purpose of a primary key is to relate records to additional data stored in other tables. In this sense, the primary key is a simple pointer between related records in different tables. As such, the primary-key value has no meaning to the user and no association to the data to which it's assigned.

The developer must apply a few rules when choosing a primary key for each table:

  • The primary key must uniquely identify each record.
  • A record's primary-key value can't be null.
  • The primary key-value must exist when the record is created.
  • The primary key must remain stable - you can't change the primary-key field(s).
  • The primary key must be compact and contain the fewest possible attributes.
  • The primary-key value can't be changed.

"Natural" keys are keys that sort of "leap out" at you as "Of course that's a good key." For instance, the ISBN number of a book is a PERFECTLY natural key if you run a bookstore or a library, but it has no particular meaning unless you look up the book in your database.

"Meaningful" keys are keys that when you look at them, you know immediately what they mean.

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.