Working with Referential Constraints | ALTER TABLE | VantageCloud Lake - Adding or Dropping PRIMARY KEY and UNIQUE Constraints - Teradata VantageCloud Lake

Lake - Working with SQL
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
jbe1714339405530.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
jbe1714339405530
ALTER TABLE ... ADD PRIMARY KEY or UNIQUE constraint requires the following privileges:
  • DROP TABLE on the table or its database
  • INDEX on the table
The following rules apply to adding and dropping PRIMARY KEY and UNIQUE constraints.
  • 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. Use this alteration only 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.

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 the USI is no longer needed to enforce the uniqueness of the primary index.