Working with Referential Constraints | ALTER TABLE | Teradata Vantage - Adding or Dropping PRIMARY KEY and UNIQUE Constraints - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
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.