17.10 - MODIFY PRIMARY - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

Add or modify a primary index or primary AMP index. You cannot modify basic table parameters or revalidate a table or join index in the same ALTER TABLE statement that includes MODIFY options.

index_name
Name for an unnamed primary index or a change to the name of a primary index. For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
(index_column_name)
Change the properties of the primary index for table. For a composite primary index, index_column_name indicates a comma-separated list of all the index columns in parenthesis. You cannot specify the begin or end columns of a derived period column in a primary index.
 The table must be empty.
You cannot alter a table to have a row-level security constraint column as a component of its primary index.
You cannot define a primary index on a column with a JSON or DATASET data type.
AMP
Rows are hash-distributed to AMPs for a column-partitioned table or join index. Column partition values are ordered on each AMP by an internal partition number and a row hash for a column-partitioned table or join index.
Optionally, the INDEX keyword can be specified with AMP for readability.
If a PRIMARY AMP clause is specified, you must specify a PARTITION BY clause that includes a column-partitioning level, either in the PRIMARY AMP clause or by itself in the index list.
INDEX
If a PRIMARY INDEX clause and a PARTITION BY clause are specified, a column-partitioning level may be included in the PARTITION BY clause. The PARTITION BY clause can be included in the PRIMARY INDEX specification or by itself in the index list. The PARTITION BY clause can include row-partitioning levels.
NOT NAMED
Drop the name of a named index.
UNIQUE
Change a nonunique primary index to a unique primary index. The table must be empty. 

A temporal table cannot have a unique primary index.
NOT UNIQUE
Change a unique primary index to a nonunique primary index. The table must be empty.