Dropping Columns | ALTER TABLE | VantageCloud Lake - Dropping Columns from a Table - Teradata VantageCloud Lake

Lake - Working with SQL
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
jbe1714339405530.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
jbe1714339405530

Learn about the different ways you can drop columns using the ALTER TABLE request for tables, including column-partitioned and normalized tables.

The following rules and restrictions apply to dropping columns from a table:
  • You cannot drop all of the columns from a table.
  • When you drop a column, the database deletes the field corresponding to the dropped column in every row in the table.
  • You cannot drop indexed columns from a table without first dropping the index on those columns.

    The following procedures explain how to drop an indexed column from a table:

Index Type How to Drop Column from Table
Primary If the table has no rows:
  1. Use the primary index or primary AMP index modification syntax of the ALTER TABLE statement to modify the table definition to create a new primary index or primary AMP index.
  2. Use ALTER TABLE table_name DROP column_name to drop the column set that was the former primary index or primary AMP index.
If table has rows:
  1. Copy the table into a newly defined table defined with a different primary index or primary AMP index using the CREATE TABLE AS syntax.
  2. Drop the original table.
  3. Rename the new table.
Legacy method for table with rows:
  1. Create a new table with the correct primary index or primary AMP index.
  2. Copy the data into the new table.

    A simple way to do this is to use an INSERT … SELECT request.

  3. Drop the original table.
  4. Rename the new table.
  • Secondary
  • Join
  1. Drop the index.
  2. Drop the index columns.
  3. Define a new index on the correct columns.
  • You cannot drop a partitioning column for a row-partitioned table without first modifying the partitioning to exclude that column from the partitioning set. Otherwise, the database returns an error.

    You cannot drop a column on which a primary, primary AMP index, secondary, or join index is defined.

  • You cannot drop columns that are referenced in the UPDATE OF clause of a trigger.
  • You can drop the identity column from an existing table.
  • You can also drop only the identity column attribute from an identity column in an existing table, retaining the column and its data.
  • You can drop LOB columns from a base table without restrictions. If you drop all the LOB columns from a table, the table is not bound by the LOB restrictions. See the "Large Object Data Types" section in Rules and Restrictions for ALTER TABLE (Basic Table Parameters).
  • You cannot drop the QITS column from a queue table.

Dropping Columns from a Normalized Table

The following rules and restrictions apply to dropping columns from a column-partitioned table.
  • A column partition number cannot be less than 1 or greater than the maximum column partition number for the table or join index. Column partition numbers may not correspond to the order that the column partitions were defined.
  • You cannot drop all of the columns of a column-partitioned table other than the 2 internal use partitions, the system returns an error to the requestor.
  • You cannot drop the 2 internal use column partitions from a table.
  • If you drop all of the columns from a column partition, the following things occur.
    • The column partition is dropped.
    • The column partition number from the dropped column partition becomes available for adding another column partition.

      The system decrements the number of defined column partitions by 1 and increments the number of column partitions that can be added by 1.

      The maximum number of column partitions and the maximum column partition number for the table are unaffected.

    • If you add multiple column partitions are in the same ALTER TABLE request that deletes column partitions, the system drops the specified column partitions before adding the new column partitions.
    • You can drop all the existing columns from a column partition, but the system does not drop the partition if you also add new columns to the column partition in the same ALTER TABLE request.
  • If you drop columns from a column partition and there are other columns in the altered column partition:
    • If you drop a column set from a column partition and that column partition has system-determined column partition format, the system redetermines the column partition format based on the size of a column partition value for the remaining set of columns in the column partition and other factors such as whether a column partition value for the column partition has fixed or variable length.

      Vantage typically determines a narrow column partition (defined as 256 or fewer bytes) to have COLUMN format and a wide column partition to have ROW format.

      You can use HELP COLUMN requests or retrieve the appropriate rows using a data dictionary view to determine the system-column partition form that Vantage chose for a column partition. See Views Reference for information about the views provided by Teradata.

    • If the column partition from which the column set was dropped has user-specified COLUMN, ROW, or SYSTEM format, Vantage does not change the format for the altered column partition.
    • Vantage assigns a different column partition number to the altered column partition.
    • The number of defined column partitions, including the 2 internal use column partitions, the number of column partitions that can be added, the maximum number of column partitions, and the maximum column partition number are not affected by these operations.

Dropping Columns from a Normalized Table

The following rules and restrictions apply to dropping columns from a normalized table.
  • You can drop any column from a normalized table, including ignore columns.
  • Dropping a normalized Period column also drops the NORMALIZE clause from the table definition.
  • Vantage renormalizes a normalized table if you drop a column that is not specified in the ignore column list from the table or when a column is added to the ignore column list.
  • You can use an ALTER TABLE request to alter the normalize_ignore_columns column list by specifying the DROP NORMALIZE option. This include the complete list - normalize clause and the ignore columns.
  • You cannot add and drop normalization from a table within the same ALTER TABLE request.

For information about adding columns to a normalized table, see Adding Columns to a Normalized Table.