15.00 - Dropping Columns From a Table - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

Dropping Columns From a Table

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

  • When you drop a column, Teradata 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 set of procedures explain what you must do to drop an indexed column from a table.


    To drop a column on which this type of index is defined …

    Follow this procedure …

    Primary: the table has no rows.


    1 Use the primary index modification syntax of the ALTER TABLE statement to modify the table definition to create a new primary index.

    2 Use ALTER TABLE table_name DROP column_name to drop the column set that was the former primary index.


    Primary: using the CREATE TABLE AS statement


    1 Copy the table into a newly defined table defined with a different primary index using the CREATE TABLE AS syntax.

    2 Drop the original table.

    3 Rename the new table.


    Primary: legacy method


    1 Create a new table with the correct primary index.

    2 Copy the data into the new table.

    A simple way to do this is to use an INSERT … SELECT request (see “INSERT … SELECT” in SQL Data Manipulation Language).

    3 Drop the original table.

    4 Rename the new table.


  • Secondary
  • Hash
  • 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, Teradata Database returns an error.
  • If you attempt to drop a column on which a primary, secondary, hash, or join index is defined, Teradata Database returns an error to the requestor.

  • 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, it is no longer bound to any LOB restrictions (see “Rules and Restrictions for Large Object Data Types” on page 37).
  • You cannot drop the QITS column from a queue table.