MODIFY PRIMARY Option - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
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, the system 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, the system 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, the system 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 Vantage does not change the uniqueness of the primary index for the table or join index.
  • You cannot specify ALL with MODIFY PRIMARY.