15.00 - Dropping Columns From a Table - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

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.