15.00 - Surrogate Keys - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Surrogate Keys

Situations occur where the identification and choice of a simple primary key is difficult, if not impossible. There might be no single column that uniquely identifies the tuples of a relation variable or, looking ahead to physical design, there might be performance or query condition considerations that argue against using a composite key. In these situations, and only in these situations, surrogate keys are an ideal solution.

As previously defined (see “Surrogate key” under “Definitions” on page 75), a surrogate key is an artificial, simple key used to identify individual entities when there is no natural key or when the situation demands a simple key, but no natural simple key exists.

Surrogate keys do not identify individual entities in a meaningful way: they are simply an arbitrary method to distinguish among those entities (see Hall, Owlett, and Todd, 1976). As a result, it is often difficult to maintain referential integrity relationships among tables with surrogate keys.

Surrogate keys are typically arbitrary system‑generated sequential integers. See “Identity Columns” on page 818 and CREATE TABLE (Column Definition Clause in SQL Data Definition Language Detailed Topics, for information about how to generate surrogate keys in Teradata Database.