ALTER TABLE Support For UDTs | Teradata Vantage - ALTER TABLE Support For UDTs - 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
2024-10-04
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jbg1472252759029
lifecycle
latest
Product Category
Teradata Vantage™
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
    • 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.