Why use a "business key" and a "primary key"?

Sometimes database designers uses two unique keys on each table, a "business key," for example orderid, and a "counter", used as the primary key. Why?

These are two different kinds of keys. The counter is a surrogate key, and the "business key" is a natural key.

All tables in a relational database should (not will, just should) have a declared primary key (PK). This key is a column or group of columns that will uniquely identify a row in the table. The PK is called a "natural key" if it can be made up of columns that are "naturally" in the table.

The PK is chosen from a group of Alternate Keys (AKs), any one of which can uniquely identify any of the rows in the table. A primary key should have several characteristics. It should rarely (preferably never) change because changes in a PK lead to cascading updates, which are serious problems for multi-user databases. With most database engines, PK values should be small, because small PK values make small foreign key (FK) values and much better key distribution in indices, all of which improve overall database performance.

Some kinds of tables have no obvious PK available (there is no natural key). A good example of this is a table that must allow completely duplicate rows to exist. Another example is a table where all columns are subject to change at the user's discretion. In these cases a surrogate key is created, which is a column that is unique all by itself. If possible, a surrogate key should not be presented to the end user, it should only be used by the database/application as the PK for the table.

Some database purists (notably my friend Joe Celko) object to surrogate keys. They point out that logically the size of the key doesn't matter. They also note that logically it doesn't matter if a cascading key update occurs. They are correct, these are implementation details, not true design issues. Unfortunately, I have to live in the real world where these details do matter, so I strongly encourage the use of surrogate keys if there is ANY suspicion that they might be needed.

For More Information

• Dozens more answers to tough database design questions from Pat Phelan
• The Best Database Design Web Links: tips, tutorials, scripts, and more
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.