To redefine the PRIMARY, PRIMARY AMP, NO PRIMARY and PARTITION BY or NOT PARTITIONED clauses for a table, use one of the following methods:
- Use the ALTER TABLE MODIFY PRIMARY or PRIMARY AMP syntax to modify the primary index or primary AMP index for the table. See the ALTER TABLE topic “MODIFY PRIMARY” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144, General Rules for the MODIFY PRIMARY Clause, and Modifying the Partitioning of a Table or Join Index.
- To change the partitioning for a table, you can specify MODIFY without specifying PRIMARY, PRIMARY AMP, or NO PRIMARY. The PRIMARY, PRIMARY AMP, or NO PRIMARY keywords are only required to modify the primary index or primary AMP index of a table.
- Create a new table with the primary index, primary AMP index, or partitioning defined properly and then use an INSERT ... SELECT to copy the data from the old table to the new table. See Redefining a Primary Index, Primary AMP Index, or Partitioning Without Using an ALTER TABLE Request.
- Use the CREATE TABLE AS syntax to:
- Create a new table with the primary index or primary AMP index and partitioning, if required.
- Copy the rows and, if desired, the statistics from the old table into the new table.
You can use ALTER TABLE to perform the following modifications to a primary index, primary AMP index, or partitioning for a table. See General Rules for the MODIFY PRIMARY Clause, Modifying the Partitioning of a Table or Join Index, and Rules For Altering a Partitioning For a Table.
| To perform this operation … | Specify … |
|---|---|
| Change a NUPI to a UPI | MODIFY UNIQUE PRIMARY or PRIMARY AMP in the MODIFY PRIMARY clause. |
| Change a UPI to a NUPI | MODIFY NOT UNIQUE PRIMARY or PRIMARY AMP in the MODIFY PRIMARY clause. |
| Change a partitioned object to a nonpartitioned object | NOT PARTITIONED in the MODIFY PRIMARY clause. The table must be empty. |
| Change a nonpartitioned object to a partitioned object | a PARTITION BY clause in the MODIFY PRIMARY clause. The table must be empty. |
| Add or drop partitioning expression ranges | an ADD RANGE or DROP RANGE clause within the Primary Index Change Options clause. See Modifying Partitioning Using the ADD RANGE and DROP RANGE Options. |
| Add or drop primary index or primary AMP index columns | MODIFY PRIMARY clause. The table must be empty. |
| Validate partitioning by regenerating table headers and correcting any errors in row partitioning | a REVALIDATE PRIMARY INDEX clause within the REVALIDATE Options clause. See General Rules and Restrictions for the REVALIDATE Option. |
If you alter a table to have a UPI and there is already a USI defined on the same column set, the system drops the USI automatically during the ALTER TABLE operation.
A primary index cannot contain any BLOB, CLOB, ARRAY, VARRAY, Period, or Geospatial columns.
You cannot change the primary index for an error table. See CREATE ERROR TABLE.