Adding Columns | ALTER TABLE | Teradata Vantage - Adding Columns To a Table - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
The following rules and restrictions apply to adding columns to any table.
  • You cannot modify a column more than once in an ALTER TABLE request.
  • When a column is added to a base table that already contains data, the system generally needs to access each row of the table to add a field for the new column. The following principles apply when adding a column to a base table that already contains rows.
    IF this phrase … IS … THEN all rows initially …
    DEFAULT specified for the new column contain the specified constant values in the field.
    WITH DEFAULT contain the system default in the field.
    DEFAULT

    WITH DEFAULT

    not specified for the new column are null for the column, and you cannot specify the NOT NULL phrase.
  • A column that is defined as NOT NULL can only be added to an empty base table if it has no DEFAULT or WITH DEFAULT phrase.
  • You cannot change the data type, nullability, or name of an indexed column.
  • You cannot change the data type, nullability, or name of a partitioning column for a partitioned table.
  • You cannot add an identity column to an existing base table, nor can you add the identity column attribute to an existing column.
  • You can add BLOB and CLOB columns to a maximum of 32 per base table. See Rules and Restrictions for Large Object Data Types.
  • You cannot add a CHARACTER, VARCHAR, or CLOB column with a server character set of KANJI1 to a table. Otherwise, the system returns an error to the requestor.