Saturday, August 18, 2007

A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.

There appear to be two definitions of a surrogate in the literature. We shall call these surrogate (1) and surrogate (2):
Surrogate (1) This definition is based on that given by Hall, Owlett and Todd (1976). Here a surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible by the user or application.
Surrogate (2) This definition is based on that given by Wieringa and de Jung (1991). Here a surrogate represents an object in the database itself. The surrogate is internally generated by the system and is invisible to the user or application.
We shall adopt the surrogate (1) definition throughout this article largely because it is more data model rather than storage model oriented. See Date (1998).
An important distinction exists between a surrogate and a primary key, depending on whether the database is a current database or a temporal database. A current database stores only currently valid data, therefore there is a one-to-one correspondence between a surrogate in the modelled world and the primary key of some object in the database; in this case the surrogate may be used as a primary key, resulting in the term surrogate key. However, in a temporal database there is a many-to-one relationship between primary keys and the surrogate. Since there may be several objects in the database corresponding to a single surrogate, we cannot use the surrogate as a primary key; another attribute is required, in addition to the surrogate, to uniquely identify each object.
Although Hall et alia (1976) say nothing about this, other authors have argued that a surrogate should have the following constraints:

the value is unique system-wide, hence never reused
the value is system generated
the value is not manipulable by the user or application
the value contains no semantic meaning
the value is not visible to the user or application
the value is not composed of several values from different domains. Definition
In a current database, the surrogate key is the primary key, generated by the database management system and not derived from any application data in the database. The only significance of the surrogate key is to act as the primary key.
A surrogate key is frequently a sequential number (e.g. a Sybase or SQL Server "identity column" or an Oracle SEQUENCE or a column defined with AUTO_INCREMENT in MySQL) but doesn't have to be. Having the key independent of all other columns insulates the database relationships from changes in data values or database design (making your database more agile) and guarantees uniqueness.
In a temporal database, it is necessary to distinguish between the surrogate key and the primary key. Typically, every row would have both a primary key and a surrogate key. The primary key identifies the unique row in the database, the surrogate key identifies the unique entity in the modelled world; these two keys are not the same. For example, table Staff may contain two rows for "John Smith", one row when he was employed between 1990 and 1999, another row when he was employed between 2001 and 2006. The surrogate key is identical (non-unique) in both rows however the primary key will be unique.
Some database designers use surrogate keys religiously regardless of the suitability of other candidate keys, while others will use a key already present in the data, if there is one.
A surrogate may also be called a:
Some of these terms describe the way of generating new surrogate values rather than the nature of the surrogate concept.
Here are some possible candidates for generating surrogates:

surrogate key
entity identifier
system-generated key
database sequence number
synthetic key
technical key
arbitrary unique identifier
Universally Unique Identifiers (UUIDs)
Globally Unique Identifiers (GUIDs)
Object Identifiers (OIDs)
Sybase or SQL Server identity column
PostgreSQL serial
MySQL AUTO_INCREMENT Surrogate key Disadvantages of Surrogate Keys

Natural key
Object identifier

No comments: