Guidelines for Selecting Primary Keys - Teradata Database

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 78) 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 78 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:

line_item
line_item_number	order_number	order_year_date
PK
	FK	FK

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:

line_item
line_item_number	order_FK_number	order_FK_year_date
PK
	FK	FK