16.20 - MODIFY PRIMARY Option - 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)

The following rules apply to the MODIFY PRIMARY INDEX or MODIFY PRIMARY AMP INDEX option:

  • If you specify MODIFY PRIMARY and the table currently does not have a primary index, you must specify a column list to indicate the columns to be included in the new primary index.

    If you do not, Teradata Database returns an error to the requestor.

  • You cannot alter a populated table with a primary-index or primary AMP index to have a different set of index columns. Otherwise, Teradata Database returns an error to the requestor.
  • If you specify UNIQUE PRIMARY INDEX, then the new primary index for the table is a UPI.

    A primary index can be unique only if all the partitioning columns, if any, are also included in the column set that defines the primary index.

    You cannot specify UNIQUE PRIMARY INDEX unless at least one of the following conditions is true for the current primary index for the table.

    • The current primary index is a UPI.
    • The primary index is defined implicitly by a PRIMARY KEY or UNIQUE constraint.
    • An existing USI is defined on the same column set as the new UPI.

      The existing USI can be specified either explicitly or implicitly by a PRIMARY KEY or UNIQUE constraint.

    • The table is not populated with rows.

      Otherwise, Teradata Database returns an error to the requestor.

  • If you specify NOT UNIQUE PRIMARY INDEX, the new primary index for the table or join index is a NUPI.
  • If you neither specify UNIQUE PRIMARY INDEX nor NOT UNIQUE PRIMARY INDEX, then Teradata Database does not change the uniqueness of the primary index for the table or join index.
  • You cannot specify ALL with MODIFY PRIMARY.