15.00 - UNIQUE Constraints - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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 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

    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

    table.

    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.
  • You cannot define a UNIQUE constraint for a non-partitioned NoPI table.

    If you attempt to do so, 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:
  • XML
  • BLOB
  • CLOB
  • XML‑based UDT
  • BLOB‑based UDT
  • CLOB‑based UDT
  • ARRAY/VARRAY
  • 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.