15.00 - PRIMARY INDEX Option - 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)

PRIMARY INDEX Option

The following rules apply to the PRIMARY INDEX option.

  • If you specify PRIMARY INDEX, one the following things must be true.
  • You also specify NOT PARTITIONED
  • You also specify a PARTITION BY clause
  • The table is currently not column‑partitioned
  • If none of these conditions is true, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify PRIMARY INDEX and the table currently does not have a primary index, you must specify a column list following the PRIMARY INDEX keywords to indicate the columns to be included in the new primary index.
  • If you do not, Teradata Database aborts the request and returns an error to the requestor.

  • If you alter a table to have a unique primary index and there is currently a unique secondary index on the same column set, Teradata Database automatically drops the unique secondary index.
  • You cannot alter a populated table with a NUPI to have a UPI on the same set of columns using a MODIFY NOT UNIQUE PRIMARY INDEX request unless there is also a unique secondary index on the same set of columns. Otherwise, Teradata Database aborts the request and returns an error to the requestor.
  • You cannot alter a populated primary‑indexed table to have a different set of primary index columns. Otherwise, Teradata Database aborts the request and returns an error to the requestor.
  • If you specify UNIQUE PRIMARY INDEX, then the new primary index for the table is a UPI.
  • A partitioning can be unique only if all its partitioning expression columns 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 aborts the request and 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.