15.00 - Redefining a Primary Index or Partitioning Without Using an ALTER TABLE Request - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

Redefining a Primary Index or Partitioning Without Using an ALTER TABLE Request

To redefine the primary index or partitioning for a table without using an ALTER TABLE request, perform a procedure similar to that described in “Procedure to Change Column Data Types” on page 80. There are several methods that you can use to achieve this, neither of which is superior to the other.

  • First method.
  • a Copy the table into a newly defined table defined with a different primary index (or with no primary index) and populate it using the CREATE TABLE … AS syntax (see “CREATE TABLE (AS Clause)” on page 656).

    b Catalog the privileges on the old table. See step 3 in “Procedure to Change Column Data Types” on page 80.

    c Drop the original table (see “DROP MACRO” on page 805 and SQL Data Definition Language Syntax and Examples).

    d Rename the new table (see “RENAME MACRO” on page 840 and SQL Data Definition Language Syntax and Examples).

    e Grant privileges on the new table (see “GRANT (SQL Form)” in SQL Data Control Language).

  • Second method.
  • a Using a different name, create a new table that specifies the new index.

    b Populate the new table using an INSERT … SELECT request (see SQL Data Manipulation Language).

    c Catalog the privileges on the old table. See step 3 in “Procedure to Change Column Data Types” on page 80.

    d Drop the original table (see “DROP MACRO” on page 805 and SQL Data Definition Language Syntax and Examples).

    e Rename the new table with that of the old table (see “RENAME MACRO” on page 840 and SQL Data Definition Language Syntax and Examples).

    f Grant privileges on the new table (see “GRANT (SQL Form)” in SQL Data Control Language).