15.00 - General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses - 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)

General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses

The following general rules and observations apply to the MODIFY [PRIMARY INDEX] clause. For the rules that apply to partitioning expressions, see “Rules For Altering the Partitioning Expression For a Table” on page 110.

  • You must specify MODIFY PRIMARY INDEX is when you are altering the component columns of the primary index for a table.
  • If you are only modifying the partitioning for a table, you need only specify MODIFY without also specifying PRIMARY INDEX.

  • You cannot alter the primary index for any of the following database objects using an ALTER TABLE request.
  • Global temporary tables
  • Volatile tables
  • Join indexes
  • Hash indexes
  • You cannot modify both the primary index or partitioning for a table and other table definitions within a single ALTER TABLE request because the operations are mutually exclusive.
  • You cannot modify both primary index or partitioning definitions and revalidate a primary index within a single ALTER TABLE request because the operations are mutually exclusive.
  • You cannot alter a populated primary‑indexed table to have a different set of primary index columns.
  • 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.
  • At least one specified option must change the definition of the primary index or the system returns an error.
  • Specifying different partitioning expressions for an existing partitioned table or join index is considered to be a valid change even if the results of the new expressions are identical to those produced by the former partitioning expressions.

  • If you do not specify a column set for the primary index, then the existing column set for the primary index is retained.
  • If the resulting primary index of a modification is unique and if there is also an existing USI on the same column set, whether explicitly or implicitly defined, then that USI and any associated PRIMARY KEY or UNIQUE constraint is dropped because it is redundant.
  • If the resulting primary index if a modification is non‑unique and the previous primary index was implicitly defined by a PRIMARY KEY or UNIQUE constraint, then Teradata Database drops that constraint.
  • The primary index or partitioning expression of a table or join index cannot be defined on columns that have any of the following data types.
  • BLOB
  • CLOB
  • JSON
  • XML
  • Period
  • Derived Period
  • ARRAY/VARRAY
  • Geospatial
  • Distinct or structured UDTs that contain BLOB, CLOB, or XML columns.
  • All other Teradata SQL data types are valid.

  • If there is a FOREIGN KEY reference to or FOREIGN KEY constraint on either the current or resulting set of primary index columns, then you can only change the name of the index. Otherwise, the request aborts and Teradata Database returns an error to the requestor.
  • If you specify a column set for the primary index of a populated table or join index, then that column set must be identical to the existing column set for the primary index.
  • Conversely, you can specify a column set for the primary index that differs from the current primary index column set only if the table is empty.

    Otherwise, Teradata Database returns an error.

  • You cannot alter a populated primary‑indexed table that currently has a NUPI to have a UPI on the same column set unless a USI is also defined on that column set.
  • If you specify a column set for the primary index of a populated table or join index that differs from a current primary index that is defined implicitly by a PRIMARY KEY or UNIQUE constraint, then Teradata Database drops that PRIMARY KEY or UNIQUE constraint.
  • If the current primary index for a table or join index is defined implicitly by a PRIMARY KEY or UNIQUE constraint and you use ALTER TABLE to specify a PARTITION BY clause, Teradata Database drops that PRIMARY KEY or UNIQUE constraint.
  • You cannot modify the primary index of a queue table to partition it.
  • Queue tables cannot have partitioned primary indexes.

  • The following rules apply to changing the name of a primary index.
  • The index_name cannot be the same as the name of any secondary index or constraint defined for the table or join index.
  • If you specify index_name, then that is the name of the primary index.
  • If you specify NOT NAMED, then the primary index has no name.
  • If you specify neither an index_name nor a NOT NAMED phrase, then no change is made to the current name.