15.00 - Procedure for Identifying Primary Keys - Teradata Database

Teradata Database Design

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

Procedure for Identifying Primary Keys

The selection of a primary key for a relation is the final step in a (possible) series of identifications of unique attribute sets as follows:

1 Identify any superkeys that exist in the attribute set.

A superkey is any set of (possibly redundant) attributes that uniquely identifies the tuples of a relation.

Every relation has at least one superkey and might have only one.

2 Eliminate any redundant, or otherwise unnecessary, attributes in the identified superkeys and produce a candidate key set.

A candidate key is a nonredundant attribute set that uniquely identifies the tuples of a relation. Note that it is possible for composite candidate keys to overlap.

By definition, every relation has at least one candidate key and often has only one candidate key.

3 Select the primary key from the set of identified candidate keys.

Selection of the primary key from a set of candidate keys is ultimately an arbitrary decision, but when there are multiple candidate keys to choose from, a good rule of thumb is to select the one having the fewest attributes, particularly if you plan to use the primary key as the primary index for the table.

See “Performance Considerations for Primary Indexes” on page 441 for specific information on selecting primary indexes to optimize retrieval and hashing performance.

The unselected candidate keys, if any, are referred to as alternate keys. Assign a UNIQUE constraint to any alternate key not selected to be the primary key for a relation to ensure its integrity with respect to the referential integrity rule (see “The Referential Integrity Rule” on page 95).

Every relation has one and only one primary key.