Dropping Columns | ALTER TABLE | Teradata Vantage - Dropping Columns From a Table - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
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 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 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.
Primary: using the CREATE TABLE AS statement
  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.
Primary: legacy method
  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. See “INSERT … SELECT” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

  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, the database returns an error.

    You cannot drop a column on which a primary, primary AMP index, secondary, hash, 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 Rules and Restrictions for Large Object Data Types.
  • You cannot drop the QITS column from a queue table.