15.00 - Surrogate Keys - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Surrogate Keys

Situations sometimes 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 rows of a table or there might be performance considerations that argue against using a composite key. In these situations, surrogate keys are an ideal solution.

A surrogate key is an artificial simple key used to identify individual rows uniquely when there is no natural key or when the situation demands a simple key, but no natural non‑composite key exists. Surrogate keys do not identify individual rows in a meaningful way: they are simply an arbitrary method to distinguish among them.

Surrogate keys are typically arbitrary system‑generated sequential integers. You can generate surrogate keys in Teradata Database using the identity column feature (see “Identity Columns” on page 537). To use an identity column to generate surrogate key values, specify the GENERATED ALWAYS and NO CYCLE options and ensure that the data type for the column is either NUMERIC(18,0) or DECIMAL(18,0).