15.00 - Adding Columns To a Table - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Adding Columns To a Table

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” on page 37).
  • You cannot add a CHARACTER, VARCHAR, or CLOB column with a server character set of KANJI1 to a table. Otherwise, Teradata Database aborts the request and returns an error to the requestor.