MODIFY PRIMARY Option - 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
2024-10-04
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jbg1472252759029
lifecycle
latest
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.