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.
- UNIQUE constraints should always be paired with a NOT NULL attribute specification.
- 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, 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 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 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.
- BLOB
- CLOB
- BLOB-based UDT
- CLOB-based UDT
- ARRAY
- VARRAY
- Period
- Geospatial