- DROP TABLE on the table or its database
- INDEX on the table
- You can add a PRIMARY KEY or UNIQUE constraint that specifies the same columns as a unique primary index (UPI).
- You can add a PRIMARY KEY or UNIQUE constraint that specifies the same columns as a primary AMP index (PA).
- You can add a PRIMARY KEY or UNIQUE constraint that specifies the same columns as a unique secondary index (USI).
- You can add a PRIMARY KEY or UNIQUE constraint that specifies the same columns as a nonunique primary index (NUPI) if the NUPI includes all the partitioning columns or there are no partitioning columns. The constraint is enforced by a unique secondary index.
- You cannot add a PRIMARY KEY or UNIQUE constraint to the (Queue Insertion TimeStamp) QITS column of a queue table.
- You cannot define a PRIMARY KEY or UNIQUE constraint for a LOB column.
- Use the DROP INDEX statement to drop unnamed UNIQUE constraints.
- You can drop a named PRIMARY KEY or UNIQUE constraint that specifies the same columns as a unique primary index (UPI).
- You can drop a named PRIMARY KEY or UNIQUE constraint that specifies the same columns as a primary AMP index (PA).
Adding a PRIMARY KEY or UNIQUE Constraint to a Table With a NUPI
For a table with a nonunique primary index (NUPI), you can only add a PRIMARY KEY or UNIQUE constraint on the same set of columns as the primary index column list if a unique secondary index (USI) is not explicitly defined on the same column set as the primary index. A PRIMARY KEY or UNIQUE constraint implicitly defines a USI on the same set of columns as those defining the primary index column list.
The system creates a USI on the specified column set, and the system can then use the index to validate that the primary index columns are unique. You should only use this alteration 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 clause to change its primary index from a NUPI to a UPI. The system drops the USI that was defined on the NUPI because it is no longer needed to enforce the uniqueness of the primary index.