16.20 - MODIFY PRIMARY - Teradata Database - Teradata Vantage NewSQL Engine

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.

Example: Modify a Primary Index Table to a Primary Index-Column Partitioned Table

CREATE TABLE pi3 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);

ALTER TABLE pi3 MODIFY PARTITION BY COLUMN;

ALTER TABLE pi3 MODIFY PRIMARY INDEX PARTITION BY COLUMN;

ALTER TABLE pi3 MODIFY PRIMARY INDEX (a) PARTITION BY COLUMN;

CREATE TABLE pi3 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;

Example: Modify a Primary Index Table to a Primary Index-Column Partitioned-RP Table

CREATE TABLE pi4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);

ALTER TABLE pi4 MODIFY
  PARTITION BY (COLUMN, RANGE_N(b BETWEEN 1 AND 10 EACH 1));

ALTER TABLE pi4 MODIFY PRIMARY INDEX
    PARTITION BY (COLUMN, RANGE_N(b BETWEEN 1 AND 10 EACH 1));

ALTER TABLE pi4 MODIFY PRIMARY INDEX (a)
  PARTITION BY (COLUMN, RANGE_N(b BETWEEN 1 AND 10 EACH 1));

CREATE TABLE pi4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a)
  PARTITION BY (COLUMN, RANGE_N(b BETWEEN 1 AND 10 EACH 1));

Example: Modify a Primary Index-Column Partitioned Table to a Primary AMP Index-Column Partitioned Table

CREATE TABLE p8 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;

ALTER TABLE p8 MODIFY PRIMARY AMP INDEX;

ALTER TABLE p8 MODIFY PRIMARY AMP INDEX (a);

ALTER TABLE p8 MODIFY PRIMARY AMP INDEX (a) PARTITION BY COLUMN;

CREATE TABLE p8 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (b) PARTITION BY COLUMN;

Example: Modify a Primary Index-Column Partitioned Table to a NoPI-Column Partitioned Table

CREATE TABLE p9 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;

ALTER TABLE p9 MODIFY NO PRIMARY INDEX;

ALTER TABLE p9 MODIFY NO PRIMARY INDEX PARTITION BY COLUMN;

CREATE TABLE p9 (a INTEGER, b INTEGER, c CHAR(10))
  NO PRIMARY INDEX PARTITION BY COLUMN;

Example: Modify a Primary Index Table to a Primary AMP Index-Column Partitioned Table

CREATE TABLE p11 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);

ALTER TABLE p11 MODIFY PRIMARY AMP INDEX PARTITION BY COLUMN;

ALTER TABLE p11 MODIFY PRIMARY AMP INDEX (a) PARTITION BY COLUMN;

CREATE TABLE p11 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (a) PARTITION BY COLUMN;

Example: Modify a Primary Index Table to a Primary AMP Index-Column Partitioned Table with a Different Index Column

CREATE TABLE p12 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);

ALTER TABLE p12 MODIFY PRIMARY AMP INDEX (b) PARTITION BY COLUMN;

CREATE TABLE p12 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (b) PARTITION BY COLUMN;

Example: Modify a Primary AMP Index Column Partitioned Table to a Primary Index Nonpartitioned Table

CREATE TABLE pt3 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (a) PARTITION BY COLUMN;

ALTER TABLE pt3 MODIFY PRIMARY INDEX (a) NOT PARTITIONED;

ALTER TABLE pt3 MODIFY PRIMARY INDEX NOT PARTITIONED;

CREATE TABLE pt3 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);

Example: Modify a Primary AMP Index Column Partitioned Table to a Primary Index Column Partitioned Table

CREATE TABLE pt4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (a) PARTITION BY COLUMN;

ALTER TABLE pt4 MODIFY PRIMARY INDEX (b);

ALTER TABLE pt4 MODIFY PRIMARY INDEX (b) PARTITION BY COLUMN;

CREATE TABLE pt4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (b) PARTITION BY COLUMN;