15.00 - Rules for Primary Keys - Teradata Database

Teradata Database Design

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

Rules for Primary Keys

There are several rules that define the bounds of primary keys. Note that these rules actually apply to all candidate keys for a relation, not just to its primary key.

The first and second rules in the following list are absolute, and the third is strongly advised:

  • Primary key attributes cannot be null.
  • This principle is known as the entity integrity rule, and it is one of the fundamental principles of relational database theory. See “The Referential Integrity Rule” on page 95 for a definition of the other fundamental integrity rule.

    By definition, nulls are not unique because they represent missing values that cannot be distinguished from one another.

    Although the database relational model does not explicitly state that alternate keys cannot be null, the constraint is implicit because a column set cannot be a potential primary key (a candidate key) if it is null or contains nulls.

  • Primary and alternate key attributes cannot contain duplicate values.
  • By definition, a primary key is a unique identifier. If it contains duplicate values, it cannot be unique (this means that multiset tables cannot have true primary keys).

  • Primary and alternate key values should never be modified.
  • This rule is neither part of the relational model, nor is it absolute, because there are occasions when primary key updates must be made.

    Whenever a primary key is updated, it is possible, and even probable, that a series of coordinated foreign key updates must also be made to maintain the consistency of the database. However, it is equally likely that those cascaded updates will never be performed, leaving the database in a state that does not reflect reality even if all the system integrity constraints have been satisfied, which is the principal reason primary key updates are discouraged so strongly.

  • Primary and alternate keys cannot be defined on columns defined with the BLOB or CLOB data types.