15.00 - Guidelines for Selecting Primary Keys - Teradata Database

Teradata Database Design

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

Guidelines for Selecting Primary Keys

This topic presents several recommendations for selecting the attributes that make up the primary key for a relation variable. These guidelines apply, the necessary changes being made, to all candidate keys:

  • Select numeric attributes as primary keys.
  • Numeric keys are both easier to produce uniquely and easier to manage than character data.

    Always assign numeric attributes as the primary key if the key is to be system-generated.

  • Whenever possible, use system-assigned keys (see “Surrogate key” under “Definitions” on page 75) to simplify the maintenance of uniqueness on the primary key attribute set. This is another factor that argues in favor of numeric keys.
  • Select primary key attributes that can remain unique for the life of a relation variable.
  • Construct primary keys on attributes that rarely, if ever, change during the life of a relation variable.
  • Never use intelligent keys.
  • An intelligent key carries semantics about the tuple it identifies.

    The expression is also used to describe a key constructed around one or more misprojected attributes that would not be in the relation variable were the database properly normalized.

    Note that intelligent keys and natural keys are not the same thing. See “Intelligent key” and “Natural key” under “Definitions” on page 75 for details.

  • Whenever relation variables are paired in a supertype-subtype relationship, always assign the same primary key to both.
  • Use a consistent convention when naming primary key attributes (see “Guidelines for Naming Columns” on page 134).
  • Because it is important to track the originating entity for attributes, always identify foreign keys with a unique code.

    For example, if you use the column naming convention described in “Guidelines for Naming Columns” on page 134, you might extend the convention as indicated in the following example:

    Suppose the relation Lineitem has the following primary key composed of the three attributes indicated by preliminary attribute names:

     

    You might consider naming the primary key attributes as follows.

  • line_item_number
  • line_item_order_FK_number
  • line_item_order_FK_year_date
  • The FK embedded in order_number and order_year_date indicate that the attributes are foreign keys that reference the order relation variable.

    When attribute naming has been completed, the primary key attribute names should look like the following relation definition fragment: