In Part 14, we introduced the concept of surrogate keys and showed how our example database of clients and policies would look if we used surrogate keys with that database. We also introduced some terminology that will help us examine the arguments for and against their use, terms such as "business key," "natural key" and "mixed key." We then went on to define two sets of semantic constraints that give surrogate keys their meaning. The minimal set consisted of three rules for the Client table and a corresponding three rules for the Policy table. The full set consisted of an additional two rules, both of which applied to both tables.
We also designated the approach to surrogate keys that we will use in the remainder of these articles as the "strong approach." The approach we will contrast it to we will designate the "standard approach," for such it is. We begin, then, by describing the standard approach and our reasons for dismissing it.
Neither the chart of version patterns nor the chart of this series are included in this article. The reason is that this discussion of surrogate keys is relatively self-contained and makes few references to earlier articles in this series.
Surrogate and Other Kinds of Keys: Definitions
-
Primary Key. The column or columns of a table in a relational database that the database management system (DBMS) recognizes as the unique identifier column or columns for that table.
-
Foreign Key. One or more columns of a table in a relational database used by the DBMS to establish a relationship between that table and another (not necessarily distinct) table. At the instance level, foreign keys relate pairs of rows such that each foreign key value is identical to a primary key value in the referenced table.
-
Surrogate Key. A primary key whose values are system-generated, and which therefore carry no business meaning. Surrogate keys are usually single-column keys.
-
Mixed Key. A multi-column primary key, some of whose columns contain system-generated values and other of whose columns contain business-meaningful values.
-
Business Key. A unique identifier for a table, all of whose values carry business meaning.
-
Natural Key. A primary key that is a business key.
Surrogate Keys: The Standard Approach
When IT practitioners speak of a database that uses surrogate keys, they are usually referring to a database some of whose tables have single-column surrogate primary keys, but other of whose tables have multi-column primary keys whose values may or may not be system assigned. Thus, they might use a single-column surrogate key for a Purchase Order table but create a mixed primary key for a Purchase Order Line Item table, one consisting of a foreign key to the Purchase Order table together with a line number. For an associative table matching up purchase orders to receipts, they would typically follow the general practice for associative tables and create a primary key by concatenating the primary keys of the Purchase Order and Receipt tables.
Let's call this the standard approach to using surrogate keys. What might justify the standard approach as the best way to assign primary keys to the tables in a database? It would have to be something like this. Whether or not surrogate keys are preferable to business keys is something that should be determined for each table. Now consider a database all of whose tables have natural keys. Among those tables, there is a subset whose primary keys do not contain any foreign keys, i.e., which do not use "identifying relationships." Call them "kernel tables." Assign a single-column surrogate key to these kernel tables.
That surrogate key will then become part of an identifying relationship in all of the other tables to which each kernel key table is related. There are two types of such other tables: dependent tables and associative tables. Dependent tables have primary keys which contain one surrogate foreign key. Associative tables have primary keys that contain two surrogate foreign keys.
