Refer to the following rules for the MODIFY PRIMARY INDEX or MODIFY PRIMARY AMP INDEX clause. For partitioning expressions, see Rules For Altering a Partitioning For a Table.
- You must specify MODIFY PRIMARY INDEX or MODIFY PRIMARY AMP INDEX when you are altering the component columns of the primary index or primary AMP index for a table.
To modify only the partitioning for a table, you can specify MODIFY without PRIMARY INDEX or PRIMARY AMP INDEX.
- You cannot alter the primary index or primary AMP index for any of the following database objects using an ALTER TABLE statement.
- Global temporary tables
- Volatile tables
- Join indexes
- Hash indexes
- You cannot modify the primary index, primary AMP index, or partitioning for a table and other table definitions within a single ALTER TABLE statement because the operations are mutually exclusive.
- You cannot alter a populated table with a primary index or primary AMP index to have a different set of primary index or primary AMP 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, the 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 a 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 or primary AMP index, the existing column set for the primary index or primary AMP index is retained.
- If the resulting primary index or primary AMP index of a modification is unique and if there is also an existing USI on the same column set, whether explicitly or implicitly defined, the USI and any associated PRIMARY KEY or UNIQUE constraint is dropped because it is redundant.
- If the resulting primary index if a modification is nonunique and the previous primary index was implicitly defined by a PRIMARY KEY or UNIQUE constraint, the system drops the constraint.
- The primary index, primary AMP 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 or primary AMP index of a populated table or join index, the column set must be identical to the existing column set for the primary index or primary AMP index.
To specify a column set for the primary index or primary AMP index that differs from the current column set for the primary index or primary AMP index, the table must be empty.
- You cannot alter a populated table with primary index 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, the system 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, the system 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 or primary AMP indexes.
Changing the Name of a Primary Index or Primary AMP Index
The following rules apply to changing the name of a primary index or primary AMP 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, that is the name of the index.
- If you specify NOT NAMED, the 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.