15.00 - ALTER TYPE - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference


Adding Attributes To a Structured UDT

The system automatically generates an associated observer and mutator method for each attribute you add to a UDT.

The following rules apply to adding attributes.

  • The UDT that you specify must be a structured UDT.
  • The specified UDT must not be referenced by any other database object.
  • An attempt to add an attribute to a UDT being referenced by some other database object, such as being used as the column data type of any table, the system returns an error to the requestor.
  • To be explicit, any of the following conditions cause the request to abort.

  • There is a table in any database for which the data type of one of the columns is the specified UDT.
  • There is a structured UDT for which the data type of one of the attributes is the specified UDT.
  • The specified UDT is referenced in a user-defined cast (see “CREATE CAST/ REPLACE CAST” on page 212).
  • There is a method or UDF in any database that references the specified UDT.
  • There is an ordering or transform group defined for the UDT (see “CREATE ORDERING/ REPLACE ORDERING” on page 416 and “CREATE TRANSFORM/ REPLACE TRANSFORM” on page 694).
  • The name of an added attribute must be different than all existing attribute names for the specified UDT.
  • Teradata Database creates a default observer and mutator method for each new attribute that you add.
  • The method signatures of the observer and mutator must be different than all existing method signatures for the specified UDT.

  • You cannot add a character data type with a server data set of KANJI1 to a structured data type. Otherwise, Teradata Database the system returns an error to the requestor.
  • This means that you, as a UDT developer, must perform all the necessary cleanup on all of the following database objects before you add any attributes to a structured UDT.

  • Casts associated with the UDT.
  • See “CREATE CAST/ REPLACE CAST” on page 212.

  • Orderings for the UDT.

  • Transforms for the UDT.

  • Tables whose columns are typed with the UDT.
  • See “CREATE TABLE” on page 508.

  • Structured UDTs that use the UDT.
  • See “CREATE TYPE (Structured Form)” on page 758.

  • UDFs and methods that use the UDT as a parameter type.
  • See “CREATE FUNCTION (External Form)/ REPLACE FUNCTION (External Form)” on page 240 and “CREATE METHOD” on page 403.

    You must also remove any dependent objects before you add attributes to a structured type. To assist you in this task, you can use a system‑installed macro found in the SYSUDTLIB database that lists all dependencies and the order in which they must be dropped.

    To do this, type the following request.

        EXEC SYSUDTLIB.HelpDependencies('UDT_name');

    where UDT_name is the name of the structured UDT to which you are adding an attribute set.

    The ability to add multiple attributes using the ADD ATTRIBUTE option is a Teradata extension to the ANSI SQL:2011 standard. You have the option of either submitting ALTER TYPE requests that comply with the ANSI SQL standard by adding only one attribute at a time, or of adding multiple attributes simultaneously using the Teradata extension to the ANSI SQL:2011 syntax.

    You cannot add more than 512 attributes at a time to a structured UDT.