15.00 - ALTER TABLE (MODIFY Option) - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

ALTER TABLE (MODIFY Option)

Redefining the Primary Index or Partitioning for a Table

There are three ways to redefine the PRIMARY INDEX/NO PRIMARY INDEX and PARTITION BY/NOT PARTITIONED clauses for a table.

  • Use the MODIFY PRIMARY INDEX syntax for ALTER TABLE to modify the primary index for the table (see the ALTER TABLE “Primary Index Modification Syntax” in SQL Data Definition Language Syntax and Examples, “General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses” on page 85), and “Miscellaneous Topics About Modifying the Partitioning of a Table or Join Index” on page 90.
  • If you only need to change the partitioning for a table, you can optionally specify MODIFY without also specifying PRIMARY INDEX. The PRIMARY INDEX keywords are only required to modify the primary index of a table.
  • Create a new table with the primary 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 or Partitioning Without Using an ALTER TABLE Request” on page 83).
  • Use the CREATE TABLE AS syntax (see “CREATE TABLE (AS Clause)” on page 656) to do the following operations.
  • a Create a new table with the primary index and partitioning, if required, defined properly.

    If the new table is to be column‑partitioned, create a new column‑partitioned table with no primary index.

    b 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 primary indexes and partitioning for a table.

     

    To perform this operation …

    Specify …

    Change a NUPI to a UPI

    MODIFY UNIQUE PRIMARY INDEX in the Primary Index Change Options clause (see “General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses” on page 85), and “Miscellaneous Topics About Modifying the Partitioning of a Table or Join Index” on page 90).

    Change a UPI to a NUPI

    MODIFY NOT UNIQUE PRIMARY INDEX in the Primary Index Change Options clause (see “General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses” on page 85), and “Miscellaneous Topics About Modifying the Partitioning of a Table or Join Index” on page 90).

    Change a partitioned object to an unpartitioned object

    NOT PARTITIONED in the Primary Index Change Options clause (see “General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses” on page 85), and “Miscellaneous Topics About Modifying the Partitioning of a Table or Join Index” on page 90 and “Rules For Altering the Partitioning Expression For a Table” on page 110).

    To do this, the table must be empty.

    Change an unpartitioned object to a partitioned object

    a PARTITION BY clause within the Primary Index Change Options clause (see “General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses” on page 85), and “Miscellaneous Topics About Modifying the Partitioning of a Table or Join Index” on page 90 and “Rules For Altering the Partitioning Expression For a Table” on page 110).

    To do this, 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 “General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses” on page 85), and “Miscellaneous Topics About Modifying the Partitioning of a Table or Join Index” on page 90 and “Modifying the Partitioning of a Table Using the ADD RANGE and DROP RANGE Options” on page 96).

    Add or drop primary index columns

    MODIFY or MODIFY PRIMARY INDEX in the Primary Index Change Options clause (see “General Rules for the MODIFY and MODIFY PRIMARY INDEX Clauses” on page 85), and “Miscellaneous Topics About Modifying the Partitioning of a Table or Join Index” on page 90).

    To do this, 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 Primary Index Change Options clause (see “General Rules and Restrictions for the REVALIDATE Option” on page 129).

    If you alter a table to have a UPI and there is already a USI defined on the same column set, Teradata Database drops the USI automatically as part of 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” on page 228).