You can define noncompressed and non-value-ordered single-table join indexes with a unique primary index. A join index that has a unique primary index is referred to as a unique join index. The primary index of a compressed join index must be based on the column_1 column set, which means there is only one instance of the column_2 column set for a given instance of column_1, so if the primary index column set in the column_1 column set were unique, there can be no row compression for that join index. Because of this, the ability to define a UPI for a compressed join index does not provide any advantages, so it is not supported.
The row hash value of a value-ordered join index corresponds to the ORDER BY column set instead of the primary index column set, and therefore does not support checking for the uniqueness of a UPI.
You can define a UPI on a join index. Because Teradata Database checks for uniqueness when new rows are inserted into the index, an INSERT or an UPDATE operation on the base table fails if the inserted or updated rows violate the uniqueness of the join index. Join index creation fails if rows of the base table or tables being indexed violate the uniqueness constraint defined by the UPI of the join index.
The value that you specify in the equality condition of the query forms the primary index key for accessing the unique join index.
For a user-defined unique join index whose primary index is explicitly defined as unique, the coverage testing and the presence of an equality condition on the primary index of the join index guarantee that the join index returns at most a single row, and therefore are sufficient to qualify the index to be used for single-row access by the Optimizer.
For information about how the Optimizer uses unique join indexes as an access path in two-AMP query plans, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
For information about temporal tables, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .