15.00 - Rules and Restrictions for Modifying Column-Partitioned Tables - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Rules and Restrictions for Modifying Column‑Partitioned Tables

ALTER TABLE support for column‑partitioned tables is the same as its support for primary‑indexed tables with a few exceptions.

The rules and restrictions for modifying column‑partitioned tables are documented in the following topics.

  • If you specify NO PRIMARY INDEX, one of the following things must be true.
  • You must specify the NOT PARTITIONED option.
  • or

  • You must specify a PARTITION BY clause.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

    NO PRIMARY INDEX is also a valid specification for unpartitioned NoPI tables and column‑partitioned tables and join indexes.

  • If you specify COLUMN partitioning in a PARTITION BY clause, one of the following things must be true.
  • You specified the NO PRIMARY INDEX option.
  • You specified neither PRIMARY INDEX nor NO PRIMARY INDEX and the table to be modified does not currently have a primary index.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify NO PRIMARY INDEX without altering partitioning, and the table to be modified currently has no primary index, Teradata Database aborts the request and returns an error to the requestor.
  • If you specify NO PRIMARY INDEX and NOT PARTITIONED, and the table to be modified currently has neither a primary index nor is partitioned, Teradata Database aborts the request and returns an error to the requestor.
  • If you specify NO PRIMARY INDEX without altering partitioning and the table to be modified currently has a partitioned primary index, Teradata Database aborts the request and returns an error to the requestor.
  • If you specify NO PRIMARY INDEX with DROP RANGE#n, ADD RANGE#n, or both, and the table to be modified currently has a partitioned primary index, Teradata Database aborts the request and returns an error to the requestor.
  • If you alter a currently primary‑indexed SET table to be a table with NO PRIMARY INDEX, Teradata Database alters its table kind to be MULTISET by default.
  • For the following cases, the table to be modified must not be populated with rows. Also see “Rules for Modifying Populated and Unpopulated Tables and Join Indexes” on page 89
  • You alter a populated primary‑indexed table to have no primary index or you alter a populated NoPI or column‑partitioned table to have a primary index.
  • If you attempt to alter a populated table in this way, Teradata Database aborts the request and returns an error to the requestor.

  • You alter a populated partitioned table with partitioning to have no partitioning.
  • If you attempt to alter a populated table in this way, Teradata Database aborts the request and returns an error to the requestor.

  • You alter a populated unpartitioned table to have partitioning.
  • If you attempt to alter a populated table in this way, Teradata Database aborts the request and returns an error to the requestor.

  • You alter populated partitioned table to have a new partitioning using a PARTITION BY clause.
  • If you attempt to alter a populated table in this way, Teradata Database aborts the request and returns an error to the requestor.

  • You alter a populated partitioned table to have a new partitioning using DROP or RANGE clauses.
  • If you attempt to alter a populated table in this way, Teradata Database aborts the request and returns an error to the requestor unless the new partitioning expressions are defined by DROP RANGE[#Ln] and ADD RANGE[#Ln] options that do not drop ranges other than from the beginning or end of the range sequence with at least one remaining range, and then does not add ranges between the resulting beginning and end of the ranges.

    Furthermore, if the table has multilevel partitioning and the modification of a partitioning expression includes dropping the NO RANGE [OR UNKNOWN] or UNKNOWN partitions, that partitioning expression must not have previously been modified.

    The resulting number of partitions for a level must be between 1 and the maximum defined for that level. Otherwise, Teradata Database aborts the request and returns an error to the requestor.