15.00 - Rules and Restrictions for Dropping Columns From a Column-Partitioned Table - 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 Dropping Columns From a Column‑Partitioned Table

The following rules and restrictions apply to dropping columns from a column‑partitioned table.

  • The ALTER TABLE syntax used to drop a column from a column‑partitioned table is the same as that used to drop a column from any other table.
  • If you attempt to drop all of the columns other than in the 2 internal use partitions from a column-partitioned table, Teradata Database aborts the request and returns an error to the requestor because you cannot drop all of the columns from a table.
  • You cannot drop the 2 internal use column partitions under any circumstances.

  • If you drop columns from a column partition and there are no other columns in the altered column partition, the following things are true.
  • Teradata Database drops the column partition.
  • Its column partition number becomes available to be used for adding another column partition.
  • The number of defined column partitions is decremented by 1 and the number of column partitions that can be added is incremented by 1.

    The maximum number of column partitions and the maximum column partition number are not affected.

  • If you add column partitions in the same ALTER TABLE request as you drop column partitions from a column‑partitioned table, Teradata Database drops the specified column partitions before it adds any new column partitions.
  • If you drop all of the existing columns from a column partition, Teradata Database does not drop the partition itself if you add new columns to the column partition in the same ALTER TABLE request.
  • If you drop columns from a column partition, but there are other columns in the altered column partition, the following things are true.
  • If the column partition has a system-determined column partition format, Teradata Database redetermines the column partition format, basing its choice of format on the size of a column partition value for the altered set of columns in the column partition and other factors such as whether a column partition value for the partition is fixed or variable length.
  • As a general rule, Teradata Database assigns a COLUMN format to narrow column partitions and a ROW format to wide column partitions.

    You can either submit a HELP COLUMN request or query an appropriate data dictionary view to determine the format that Teradata Database chose for a column partition.

  • If the column partition has user-specified COLUMN, ROW, or SYSTEM format, the format is unchanged for the altered column partition.
  • The altered column partition is assigned to a different column partition number.
  • The number of defined column partitions (which includes the 2 internal use column partitions), the number that can be added, the maximum number of column partitions, and the maximum column partition number are not affected.
  • A column partition number cannot be less than 1 or greater than the maximum column partition number for the table or join index. Keep in mind that column partition numbers might not correspond to the order in which the column partitions were defined for an object.