16.20 - Adding or Dropping PRIMARY KEY and UNIQUE Constraints - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

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. 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.