15.00 - UNIQUE Constraints - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

UNIQUE Constraints

UNIQUE constraints specify that the column set they modify must contain unique values. Teradata Database implements UNIQUE constraints as either a unique secondary indexes or as a single‑table join index.

The following rules apply to UNIQUE constraints.

  • UNIQUE constraints should always be paired with a NOT NULL attribute specification.
  • See “Nullable Columns Are Valid for Unique Indexes” on page 653.

  • UNIQUE constraints can be defined at column-level (simple) or at table‑level (composite).
  • Column-level UNIQUE constraints refer only to the column on which they are specified.
  • Table-level UNIQUE constraints can be defined on multiple columns using a column name list.
  • A table‑level UNIQUE constraint can be defined on a maximum of 64 columns.
  • A maximum of 100 table-level constraints can be defined for any table.
  • UNIQUE constraints cannot be defined on a global temporary trace table.
  • You can define a UNIQUE constraint for a column‑partitioned table.
  • You cannot define a UNIQUE constraint for an unpartitioned NoPI table.

    Otherwise, Teradata Database does not return an error, but instead converts the UNIQUE 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 UNIQUE constraint specification, but instead returns a UNIQUE NOT NULL secondary index or single‑table join index specification on the column set that had been specified for the UNIQUE constraint.

    Note that this is different from the ordinary implementation of a UNIQUE 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 UNIQUE constraint, it does not simply implement the constraint as a USI.

  • UNIQUE constraints cannot be defined on columns defined with any of the following data types.
  • BLOB
  • CLOB
  • BLOB‑based UDT
  • CLOB-based UDT
  • ARRAY
  • VARRAY
  • Period
  • Geospatial