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.
Mentioned in ?
References in periodicals archive ?
Surrogate keys look like object identifiers (as they are commonly used in the object model) in many respects, but Date makes a distinction between surrogate keys and object identifiers and rightly concludes that, whilst object identifiers perform some of the same functions as surrogate keys, they carry a lot of additional baggage with them, and thus are not the same thing as pointers.
Sapior Redbridge interoperates with existing ETL infrastructure to accelerate and simplify surrogate key lookup and generation, the single longest component occupying 30-50% of the ETL processing time.
In addition to high performance speeds and ease of use and administration, other important selection factors included Redbridge's ability to support multiple surrogate keys per row of input data, execute concurrent sessions on the same surrogate keys, and operate either as an Informatica PowerCenter extension or as a native Unix utility.
Enhance performance by reducing the required number of passes through its data -- Enhance performance by concurrently processing multiple data streams -- Unify banking key formats across its many business units -- Protect/Insulate its technical key structures from changes in external key formats -- Generate compact surrogate keys to improve indexing and join processing time and to reduce storage requirements
Designed to meet the unique needs of a wide range of corporate data environments, Redbridge supports all input datatypes, accepts records of different lengths or that change format over time, processes multiple surrogate keys per row and produces variable surrogate key formats.
To download a Sapior whitepaper discussing the nature of the surrogate key problem, visit http://www.
Used by leading companies like UBS AG, Sapior Redbridge is a high performance surrogate key mapping tool for data warehouses and marts.