15.00 - Function of Distinct UDTs - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

Function of Distinct UDTs

The concept of distinct UDTs is analogous to the typedef construct in the C and C++ programming languages. Unlike predefined SQL data types, however, which are only moderately strongly typed, distinct UDTs are strongly typed, which makes them ideal for enforcing domains. Note that you cannot declare CHECK constraints on UDT columns, so the UDT‑as‑domain concept cannot be fully implemented.

For example, you might want to define euro and USDollar as two different distinct UDTs even though both are internally represented as DECIMAL numbers. If euro and USDollar are defined as separate distinct data types, they cannot be compared directly or used interchangeably. In this example, a euro value cannot be compared with or stored in a USDollar type column.

A distinct UDT shares its internal representation with a predefined data type (referred to as its source type), but it is considered to be a separate and incompatible type for most operations. You cannot define a distinct UDT based on any of the Period data types. Distinct UDTs encapsulate their underlying predefined data type. This means that operations and behaviors associated with the underlying data type are not valid, and users of a distinct UDT are only able to interact with its encapsulated value by means of user‑ or system‑defined transforms, orderings, and castings, and manipulations by means of instance methods (see “CREATE TRANSFORM/ REPLACE TRANSFORM” on page 694, “CREATE ORDERING/ REPLACE ORDERING” on page 416, “CREATE CAST/ REPLACE CAST” on page 212, and “CREATE METHOD” on page 403, respectively).

You can drop any of the system‑generated semantics for a UDT (see “System‑Generated Default Functionalities For Distinct UDTs” on page 751) and replace them with your own definitions using the following SQL statements:

  • DROP CAST (see “DROP CAST” on page 793)
  • DROP ORDERING (see “DROP ORDERING” on page 806)
  • DROP TRANSFORM (see “DROP TRANSFORM” on page 812)
  • Teradata Database supports the definition of instance methods for distinct UDTs, which is an extension to the ANSI SQL standard. For example, you can define an instance method named round() to use with a new distinct UDT.