16.20 - ALTER TABLE Support For UDTs - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)
You can use ALTER TABLE to do any of the following things with UDT columns.
  • Add a UDT column to a table.

    A LOB UDT column counts as one LOB column even if the UDT is a structured type with multiple LOB attributes. The limit of 32 LOB columns includes both predefined type LOB columns and LOB UDT columns.

    A UDT must have both its ordering and its transform functionality defined before you can add a column typed with that UDT to a table definition.

    If you attempt to add such a column to a table definition without first defining its ordering and transform characteristics, the system returns an error to the requestor.

    Be aware that each UDT column you add to a table definition subtracts approximately 80 bytes from the available space in the table header. The total number of UDT columns that can be defined for a table is dependent on the presence of other features that also consume space in the table header such as indexes, compression, and so on.

    In the absence of any other optional features that occupy table header space, the upper limit on the number of UDT columns that can be defined, assuming a fat table header is approximately 1600 columns. See Teradata Vantage™ - Database Design, B035-1094 for information about thin and fat table headers and the respective table header space taken up by various database features.

  • Drop a UDT column from a table.
  • Modify the attributes of a UDT column.
    You can only modify the following set of attributes for a UDT column.
    • [NOT] NULL
    • FORMAT

      The FORMAT string must be valid for the external type of the UDT (see CREATE TRANSFORM and REPLACE TRANSFORM).

      If you do not specify a format explicitly, the system applies the default display format of the external data type.

    • TITLE
    • NAMED
    • You cannot modify the following attributes for UDT columns.
      • COMPRESS
      • DATE
      • DEFAULT numeric_value
      • TIME
      • USER
  • You cannot modify a table to have a column attributed with any of the following set of constraints to reference a UDT column.
  • If a table is partitioned, its partitioning expressions cannot reference UDT columns.