16.20 - UNIQUE Constraints - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

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.

  • 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 a nonpartitioned 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 statement 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