UNIQUE Constraints - Teradata Database

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

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.