15.00 - Keys and Indexes - Teradata Database

Teradata Database Design

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

Keys and Indexes

Definition of a Key

There are several different meanings for the term key in relational database management. When reading this manual, you should always assume that if the term key is used without a qualifying adjective, the reference is to the primary key, or PK, for a table.

The following table defines the various uses of key in the context of relational database management in general or Teradata Database in particular. Note that in every case, a key is an identifier and not a retrieval mechanism. In every case, you should read the word field to mean “one field or several fields in combination.” The expression column set is used throughout this manual in the same way; to indicate that the table object under discussion, whether it be a key or index, consists of one or more columns.

 

Term

Definition

Alternate key

Any candidate key not selected to be the primary key for a table.

Candidate key

A field that uniquely identifies a row.

Every normalized table has at least one candidate key, and most have only one. When multiple candidate keys are identified during logical design, one is selected to be the primary key and the candidate keys not selected to be the primary key are then referred to as alternate keys.

Child key

A foreign key that is a primary key or alternate key in another table.

Foreign key

A field that corresponds to the primary key or alternate key of a different, but related, table.

Foreign keys are used to maintain referential integrity. They are often useful as NUPIs when you want rows from two or more related tables to hash to the same AMP.

Grouping key

A field used to group the rows of a table in a particular way.

Hash key

A field used to compute the rowhash value for a row.

Natural key

The representation of a real world tuple identifier in a relational database. For example, a common identifier of employees in a corporation is a unique employee number. An employee is assigned an employee number whether that information is stored within the database or not.

Natural keys are sometimes confused with intelligent keys, but they are very different concepts.

Compare with “Surrogate key” in the last row of this table.

Order key

A field used to store the rows of a table in a particular order.

Parent key

A primary key or alternate key that is a foreign key in another table.

Primary key

A field that uniquely identifies a row.

A primary key can never be null.

Search key

A field used to match a corresponding field in a searched table.

Sort key

A field used to sort the rows of a table. Note that the sorted table in this case is not necessarily a base table; for example, it can be an intermediate result such as a spool file.

Surrogate key

An arbitrary, system‑generated, simple numeric key. Often used when a natural key is otherwise difficult or impossible to define for a table or when the situation demands a non‑composite primary key, but no natural non‑composite exists.

Compare with “Natural key” in the seventh row of this table.