ALTER TABLE Support For UDTs | Teradata Vantage - ALTER TABLE Support For UDTs - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
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.