UNIQUE Constraints - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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