Editing Surrogate key (section)

== 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 [[Universally unique identifier|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 [[Adaptive Server Enterprise|Sybase]] or [[Microsoft SQL Server|SQL Server]] "identity column", a [[PostgreSQL]] or [[Informix]] <code>serial</code>, an [[Oracle Corporation|Oracle]] or SQL Server <code>SEQUENCE</code> or a column defined with <code>AUTO_INCREMENT</code> in [[MySQL]]). Some databases provide [[UUID]]/[[GUID]] as a possible data type for surrogate keys (e.g. PostgreSQL <code>UUID</code><ref>{{cite web | url=http://www.postgresql.org/docs/current/static/datatype-uuid.html | title=8.12. UUID Type | date=9 May 2024 }}</ref> or SQL Server <code>UNIQUEIDENTIFIER</code><ref>{{cite web | url=http://msdn.microsoft.com/en-us/library/ms187942.aspx | title=Uniqueidentifier (Transact-SQL) - SQL Server | date=23 May 2023 }}</ref>).

Having the key independent of all other columns insulates the database relationships from changes in data values or database design<ref>{{FOLDOC|Surrogate+key}}</ref> (making the database more [[agile software development|agile]]) and guarantees uniqueness.

In a [[temporal database]], it is necessary to distinguish between the surrogate key and the [[natural key|business key]]. Every row would have both a business key and a surrogate key. The surrogate key identifies one unique row in the database, the business key identifies one unique entity of the modeled world. One table row represents a slice of time holding all the entity's attributes for a defined timespan. Those slices depict the whole lifespan of one business entity. For example, a table ''EmployeeContracts'' may hold temporal information to keep track of contracted working hours. The business key for one contract will be identical (non-unique) in both rows however the surrogate key for each row is unique.

{| class="wikitable"
|-
! SurrogateKey !! BusinessKey !! EmployeeName !! WorkingHoursPerWeek !! RowValidFrom !! RowValidTo
|-
| 1 || BOS0120 || John Smith || 40 || 2000-01-01 || 2000-12-31
|-
| 56 || P0000123 || Bob Brown || 25 || 1999-01-01 || 2011-12-31
|-
| 234 || BOS0120 || John Smith || 35 || 2001-01-01 || 2009-12-31
|}

Some database designers use surrogate keys systematically regardless of the suitability of other [[candidate key]]s, while others will use a key already present in the data, if there is one.

Some of the alternate names ("system-generated key") describe the way of ''generating'' new surrogate values rather than the ''nature'' of the surrogate concept.

Approaches to generating surrogates include:

* [[Universally Unique Identifier]]s (UUIDs)
* [[Globally Unique Identifier]]s (GUIDs)
* [[Object identifier|Object Identifier]]s (OIDs)
* [[Adaptive Server Enterprise|Sybase]] or SQL Server identity column <code>IDENTITY</code> OR <code>IDENTITY(n,n)</code>
* [[Oracle Corporation|Oracle]] <code>SEQUENCE</code>, or <code>GENERATED AS IDENTITY</code> (starting from version 12.1)<ref>{{Cite web|url=http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402|title=Database SQL Language Reference}}</ref>
* SQL Server <code>SEQUENCE</code> (starting from SQL Server 2012)<ref>{{cite web | url=https://msdn.microsoft.com/en-us/library/ff878091.aspx | title=CREATE SEQUENCE (Transact-SQL) - SQL Server | date=29 December 2022 }}</ref> 
* [[PostgreSQL]] or [[IBM Informix]] serial
* MySQL <code>AUTO_INCREMENT</code>
* [[SQLite]] <code>INTEGER PRIMARY KEY</code> (if <code>AUTOINCREMENT</code> is used it will prevent the reuse of numbers that have already been used but are available)<ref>{{cite web |url=https://sqlite.org/autoinc.html |title=SQLite Autoincrement |author=<!--Not stated--> |date=2017-02-02 |website=SQLite |publisher= |access-date=2022-12-02 |quote=}}</ref>
* AutoNumber data type in [[Microsoft Access]]
* <code>AS IDENTITY GENERATED BY DEFAULT</code> in [[IBM Db2]] and [[PostgreSQL]].
* Identity column (implemented in [[Data definition language|DDL]]) in [[Teradata]]
* Table Sequence when the sequence is calculated by a procedure and a sequence table with fields: id, sequenceName, sequenceValue and incrementValue