surrogate key


Also found in: Acronyms, Wikipedia.

surrogate key

(database)
A unique primary key generated by the RDBMS that is not derived from any data in the database and whose only significance is to act as the primary key.

A surrogate key is frequently a sequential number (e.g. a Sybase "identity column") 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 and guarantees uniqueness.

Some database designers use surrogate keys religiously regardless of the suitability of other candidate keys. However, if a good key already exists, the addition of a surrogate key will merely slow down access, particularly if it is indexed.

Compare: intelligent key.
This article is provided by FOLDOC - Free Online Dictionary of Computing (foldoc.org)
Mentioned in ?
References in periodicals archive ?
surrogate key debate; however, it is important to know that although surrogate keys may uniquely identify the record, they do not always uniquely identify the data.
There is also a secondary question of whether things whose properties are recorded in a database should be identified by keys or by surrogate keys. A key is some unique combination of a thing's existing properties, whilst a surrogate key is a new property assigned as the thing enters the database, solely for the purpose of identifying it and for no other purpose.
The initial data sets created off the acute care data may derive the surrogate key for an encounter record from a combination of patient account number and discharge date.
However, with one or more of the dimensions degenerated, it is not likely that a surrogate key was created that represents the items within the degenerated dimensions.
Proponents of the surrogate key argue that a concise, manageable key is easier to deal with than an unwieldy multicolumn key.
As this implies a large scale implementation, surrogate keys are used to ensure the data warehouse expansion over time.
Standardization should include information which addresses when and how natural keys or surrogate keys are used.