17.10 - UNIQUE Constraints - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

UNIQUE constraints specify that the column set they modify must contain unique values. Vantage 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, the system 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 Vantage 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