15.00 - Adding or Dropping PRIMARY KEY and UNIQUE Constraints - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Adding or Dropping PRIMARY KEY and UNIQUE Constraints

ALTER TABLE … ADD UNIQUE/PRIMARY KEY 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 constraint if and only if no primary key is already defined for the table.
  • There are no exceptions to this rule.

    The added primary key always maps to a unique secondary index (USI).

  • You cannot add a PRIMARY KEY or UNIQUE constraint to the QITS column of a queue table.
  • You can drop a PRIMARY KEY constraint only if it is mapped to a unique secondary index (USI). You cannot drop a PRIMARY KEY constraint if it is mapped to the primary index of a table.
  • For a table with a 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 USI is not explicitly defined on the same column set as the primary index. Any such PRIMARY KEY or UNIQUE constraint implicitly defines a USI on the same set of columns as those defining the primary index column list.
  • When you do this, Teradata Database 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, Teradata Database 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. This action causes Teradata Database to drop the USI that had been defined on the old NUPI because it is no longer needed to enforce the uniqueness of the primary index.

  • Use the DROP INDEX statement to drop unnamed UNIQUE constraints.
  • A PRIMARY KEY or UNIQUE constraint cannot be defined for a LOB column.