MODIFY PRIMARY - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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.