Surrogate key

A surrogate key (or synthetic key, pseudokey, entity identifier, factless key, or technical 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, unlike a natural (or business) key. There are at least two definitions of a surrogate: Surrogate (1) – Hall, Owlett and Todd (1976) A surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible to the user or application. Surrogate (2) – Wieringa and De Jonge (1991) 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. The Surrogate (1) definition relates to a data model rather than a storage model and is used throughout this article. See Date (1998). An important distinction between a surrogate and a primary key depends on whether the database is a current database or a temporal database. Since a current database stores only currently valid data, there is a one-to-one correspondence between a surrogate in the modeled world and the primary key of the database. In this case the surrogate may be used as a primary key, resulting in the term surrogate key. In a temporal database, however, 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 al. (1976) say nothing about this, others have argued that a surrogate should have the following characteristics: the value is 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.