15.00 - ALTER TABLE Support For UDTs - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

ALTER TABLE Support For UDTs

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.

    Note: 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 Database Design 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/ REPLACE TRANSFORM” on page 694).

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

  • TITLE
  • NAMED
  • DEFAULT NULL
  • You cannot modify the following attributes for UDT columns.
  • CASESPECIFIC
  • CHARACTER SET
  • [CHECK] CONSTRAINT
  • COMPRESS
  • DATE
  • DEFAULT numeric_value
  • TIME
  • UPPERCASE
  • USER
  • WITH DEFAULT
  • You cannot modify a table to have a column attributed with any of the following set of constraints to reference a UDT column.
  • [CHECK] CONSTRAINT
  • FOREIGN KEY … REFERENCES
  • If a table is partitioned, its partitioning expressions cannot reference UDT columns.