15.00 - PRIMARY KEY Constraints - Teradata Database

Teradata Database Design

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

PRIMARY KEY Constraints

PRIMARY KEY constraints specify the primary key column set in a table definition. Teradata Database uses primary keys to enforce both row uniqueness and referential integrity.

Whether a PRIMARY KEY constraint is treated as a column-level constraint or a table-level constraint depends on whether the primary key is simple or composite.

The following rules apply to PRIMARY KEY constraints.

  • Only one primary key can be defined per table.
  • The following table explains the column limits for column‑level and table‑level primary key constraints.
  •  

    IF the PRIMARY KEY constraint is …

    THEN you must define it at this level …

    simple, or defined on a single column

    column.

    You can define a simple PRIMARY KEY constraint at table‑level, but there is no reason to do so.

    composite, or defined on multiple columns

    table.

    Defining a table‑level PRIMARY KEY constraint is the only way you can create a multicolumn primary key.

  • Defining a primary key for a table is never required, though it is recommended for documentation purposes as part of a policy of enforcing data integrity in those cases where the logical primary key is not chosen to be the unique primary index.
  • A PRIMARY KEY constraint cannot be defined on the same column set as the set used to define the unique primary index for a table.
  • If you attempt to define a PRIMARY KEY constraint on the same column set that defines the primary index for a table, the request aborts and returns a message to the requestor.

  • A primary key can be defined on a maximum of 64 columns.
  •  

    PRIMARY KEY constraints are treated as …

    When the primary key is defined on this many columns …

    column-level constraints

    1

    table-level constraints

    > 1

    A maximum of 100 table-level constraints can be defined for any table.

  • When a PRIMARY KEY constraint is defined, it is implemented physically as either a UNIQUE NOT NULL secondary index or as a single‑table join index.
  • If you attempt to define a PRIMARY KEY constraint on the same column set that defines the primary index for a table, the request aborts and returns a message to the requestor.

    Note that in physical database design, candidate keys, whether chosen to be a primary key or not, are always defined internally as either a UNIQUE NOT NULL secondary index or as a single‑table join index.

  • You cannot define a PRIMARY KEY constraint for a NoPI table.
  • If you attempt to do so, Teradata Database does not return an error, but instead converts the PRIMARY KEY constraint specification to a UNIQUE NOT NULL secondary index specification. As a result of this conversion, if you submit a SHOW TABLE request on such a table, the create text that the system returns does not show a PRIMARY KEY specification, but instead returns a UNIQUE NOT NULL secondary index specification on the column set that had been specified for the primary key.

    Note that this is different from the ordinary implementation of a PRIMARY KEY constraint column set as a UNIQUE NOT NULL secondary index because Teradata Database actually changes the stored create text for a NoPI table defined with a PRIMARY KEY constraint, it does not simply implement the constraint as a USI.

  • PRIMARY KEY constraints cannot be defined on columns defined with any of the following data types.
  • XML
  • BLOB
  • CLOB
  • XML‑based UDT
  • BLOB‑based UDT
  • CLOB‑based UDT
  • ARRAY/VARRAY
  • Period
  • Geospatial
  • JSON
  • PRIMARY KEY constraints cannot be defined on a global temporary trace table.
  • You cannot define a PRIMARY KEY on a row‑level security‑protected column.
  • Other than providing documentation, PRIMARY KEY constraints per se have little value except for establishing referential integrity constraints (see “Foreign Key Constraints” on page 644). Even this usage is generally of no use, because the logical primary key for a table is frequently declared to be the unique primary index of the physical table it models, and you cannot declare a column set to be both a unique primary index and a primary key.

    For applications where a logical single‑column primary key is not chosen to be the unique primary index, use the UNIQUE NOT NULL constraint in its place.