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
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
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
You cannot alter a populated primary‑indexed table to have a different set of primary
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
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.
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
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
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
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.