15.10 - MODIFY PRIMARY - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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 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 defined with the JSON 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

This example modifies the following table definition:
CREATE TABLE pi3 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);
Execute any one of the following ALTER TABLE statements on the empty table:
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;
The following table definition results:
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

This example modifies the following table definition:
CREATE TABLE pi4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);
Execute any one of the following ALTER TABLE statements on the empty table:
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));
The following table definition results:
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

This example uses the following table definition:
CREATE TABLE p8 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;
Execute any one of the following ALTER TABLE statements on the empty table:
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;
The following table definition results:
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

This example uses the following table definition:
CREATE TABLE p9 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;
Execute one of the following ALTER TABLE statements on the empty table:
ALTER TABLE p9 MODIFY NO PRIMARY INDEX;
ALTER TABLE p9 MODIFY NO PRIMARY INDEX PARTITION BY COLUMN;
The following table definition results:
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

This example uses the following table definition:
CREATE TABLE p11 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);
Execute one of the following ALTER TABLE statements on the empty table:
ALTER TABLE p11 MODIFY PRIMARY AMP INDEX PARTITION BY COLUMN;
ALTER TABLE p11 MODIFY PRIMARY AMP INDEX (a) PARTITION BY COLUMN;
The following table definition results:
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

This example uses the following table definition:
CREATE TABLE p12 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);
Execute the following ALTER TABLE statement on the empty table:
ALTER TABLE p12 MODIFY PRIMARY AMP INDEX (b) PARTITION BY COLUMN;
The following table definition results:
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

This example uses the following table definition:
CREATE TABLE pt3 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (a) PARTITION BY COLUMN;
Execute one of the following ALTER TABLE statements on the empty table:
ALTER TABLE pt3 MODIFY PRIMARY INDEX (a) NOT PARTITIONED;
ALTER TABLE pt3 MODIFY PRIMARY INDEX NOT PARTITIONED;
The following table definition results:
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

This example uses the following table definition:
CREATE TABLE pt4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (a) PARTITION BY COLUMN;
Execute one of the following ALTER TABLE statements on the empty table:
ALTER TABLE pt4 MODIFY PRIMARY INDEX (b);
ALTER TABLE pt4 MODIFY PRIMARY INDEX (b) PARTITION BY COLUMN;
The following table definition results:
CREATE TABLE pt4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (b) PARTITION BY COLUMN;