UNIQUE - Teradata Vantage - Analytics Database

A column or column set is unique, that is, any two rows in the table cannot have the same value in the uniquely-constrained column. The specified columns must be defined as NOT NULL.

You can specify a UNIQUE constraint as a:

column attribute on a single column or column set
table attribute, except for volatile tables

You cannot specify UNIQUE constraints on columns with the following data types:

BLOB
BLOB UDT
CLOB
CLOB UDT
VARIANT_TYPE
ARRAY
VARRAY
Period
XML
Geospatial
JSON
DATASET

The UNIQUE constraint uses a unique secondary index for nontemporal tables and a single-table join index for most temporal tables. For information about temporal tables and temporal syntax, see Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182.

Any system-defined secondary or single-table join indexes used for a UNIQUE constraint count toward the collective maximum of 32 secondary, hash, and join indexes per table. This includes the system-defined secondary indexes used for UNIQUE constraints.

UNIQUE constraints ensure that the uniqueness of alternate keys is enforced by the system. Columns with UNIQUE constraints can be used to create referential integrity relationships with other tables.

If a row-level security 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 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 table.

UNIQUE constraints are valid for nontemporal and temporal tables.

For a complete list of the rules for implicitly defined unique indexes, see the discussion of primary index defaults in Teradata Vantage™ - Database Design, B035-1094.

The implicitly defined index is a unique primary index if all of the following conditions are true:

An explicit primary index is not specified.
An explicit primary key is not specified.
This is the first unique constraint defined for the table.

If none of the previous conditions is true, the implicitly defined index is a unique secondary index.

For a temporal table, the implicitly defined index is a system-defined single-table join index. For more information, see Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182.

When a UNIQUE constraint is defined for a normalized table, Vantage validates the constraint with normalized rows. If the normalized row violates the UNIQUE constraint, the system returns an error message to the requestor.

For an unnamed UNIQUE column constraint, use this syntax:

UNIQUE

CONSTRAINT constraint_name

For a named UNIQUE column constraint, use this syntax:

CONSTRAINT constraint_name UNIQUE

For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

(column_name)

If you specify more than one column_name, the unique constraint is based on the combined values of the column set.

For an unnamed UNIQUE table constraint, use this syntax:
```
CONSTRAINT UNIQUE (column_name)
```
For a named UNIQUE table constraint, use this syntax:
```
CONSTRAINT constraint_name UNIQUE (column_name)
```

When you specify UNIQUE as a table constraint, the constraint for the table can be defined on a maximum of 64 columns.