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


Dig Deeper on Oracle database administration

Data Management
Business Analytics
SearchSAP
TheServerSide.com
  • The 3 daily Scrum questions

    The 2020 Scrum Guide removed all references to the three daily Scrum questions, but does that mean you shouldn't ask them anymore?

  • Why WebAssembly? Top 11 Wasm benefits

    Latency and lag time plague web applications that run JavaScript in the browser. Here are 11 reasons why WebAssembly has the ...

  • Why Java in 2023?

    Has there ever been a better time to be a Java programmer? From new Spring releases to active JUGs, the Java platform is ...

Data Center
Content Management
HRSoftware
Close