Surrogate Key - Surrogates in Practice

Surrogates in Practice

In a current database, the surrogate key can be 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. It is also possible that the surrogate key exists in addition to the database-generated UUID (for example, an HR number for each employee other than the UUID of each employee).

A surrogate key is frequently a sequential number (e.g. a Sybase or SQL Server "identity column", a PostgreSQL or Informix serial, 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 the 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 systematically 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 key may also be called a synthetic key, an entity identifier, a system-generated key, a database sequence number, a factless key, a technical key, or an arbitrary unique identifier. Some of these terms describe the way of generating new surrogate values rather than the nature of the surrogate concept.

Approaches to generating surrogates include:

• Universally Unique Identifiers (UUIDs)
• Globally Unique Identifiers (GUIDs)
• Object Identifiers (OIDs)
• Sybase or SQL Server identity column IDENTITY OR IDENTITY(n,n)
• Oracle SEQUENCE
• PostgreSQL or IBM Informix serial
• MySQL AUTO_INCREMENT
• AutoNumber data type in Microsoft Access
• AS IDENTITY GENERATED BY DEFAULT in IBM DB2
• Identity column (implemented in DDL) in Teradata