You can create a USI on the same column set that defines a nonunique primary index or primary AMP index for a table to enforce uniqueness.
The system can use the USI to validate that the primary index or primary AMP index columns are unique. You should only use such a USI as a temporary solution for making a NUPI unique. When you alter a table in this way, the system returns a warning message and the CREATE TABLE SQL text returned by a SHOW TABLE request is not valid.
You should immediately alter the table using the MODIFY PRIMARY INDEX clause to change its primary index from a NUPI to a UPI. See ALTER TABLE (Basic Table Parameters). The system drops the USI that had been defined on the old NUPI because it is no longer needed to enforce the uniqueness of the primary index.
When this redundant USI has been created, you can then use ALTER TABLE to change the primary index on the table from a NUPI to a UPI. You cannot make this conversion for row-partitioned PIs unless all partitioning columns are also defined in the primary index column list.
You can create a value-ordered NUSI on the same column set that defines the primary index or primary AMP index. This is valid for a row-partitioned PI, regardless of whether the primary index column set includes all the columns used in the partitioning expression.
You can create a non-value-ordered NUSI on the same column set that defines the primary index for a row-partitioned PI table when the primary index column set does not include all the columns specified for the partitioning expression.
Defining a USI or NUSI on the same column set as the primary index or primary AMP index for a PPI table may provide better performance for accesses to that table.