UNIQUE Constraints - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

UNIQUE Constraints

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

The following rules apply to UNIQUE constraints:

  • UNIQUE constraints should always be specified with a NOT NULL attribute specification.
  • Otherwise, it is possible for a single null to be inserted into a uniquely constrained column. The semantics of a unique null “value” are uncertain at best, and almost certainly violate the intent of the uniqueness constraint.

  • UNIQUE constraints can be defined at column-level (simple) or at table‑level (composite).
  • The following table explains the column limits for column‑level and table‑level primary key constraints.


    IF the UNIQUE constraint is …

    THEN you must define it at this level …

    simple, or defined on a single column


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

    composite, or defined on multiple columns


    Defining a table‑level constraint is the only way you can create a multicolumn UNIQUE constraint.

  • Column-level UNIQUE constraints refer only to the column on which they are specified.
  • Table-level UNIQUE constraints can be defined on multiple columns by specifying 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.
  • You can define a UNIQUE constraint for a column‑partitioned table.
  • UNIQUE 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
  • Period
  • Geospatial
  • JSON
  • UNIQUE constraints cannot be defined on a global temporary trace table.
  • If a row‑level security‑protected table is defined with a UNIQUE constraint, enforcement of the constraint does not execute any security policy defined for the table. UNIQUE constraints are applicable to all rows in a row‑level security‑protected table, not just to user‑visible rows.
  • You cannot define a UNIQUE constraint on a row‑level security constraint column of a row‑level security‑protected table.