17.10 - Surrogate Keys - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

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 the database using the identity column feature (see Identity Columns). 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).