DROP TYPE statement

Use the DROP TYPE statement to remove a user-defined distinct or opaque data type from the database. (You cannot use this statement to remove a built-in data type or a ROW data type.)

This statement is an extension to the ANSI/ISO standard for SQL.


1  DROP TYPE? IF EXISTS?   owner   .   data_type  RESTRICT

Element Description Restrictions Syntax
data_type Name of distinct or opaque data type to be removed Must be an existing user-defined DISTINCT or OPAQUE type in the local database. Cannot be a built-in data type. Identifier
owner Authorization identifier of the data type owner Must own data type Owner name


To drop a distinct or opaque data type with the DROP TYPE statement, you must be the owner of the data type or have the DBA privilege. When you use this statement, you remove the data type definition from the database (in the sysxtdtypes system catalog table). In general, this statement does not remove any definitions of casts or of support functions associated with that data type.
Important: When you drop a distinct type, the database server automatically drops the two explicit casts between the distinct type and the type on which it is based.

If you include the optional IF EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if no user-defined distinct or opaque data type of the specified name is registered in the current database.

The DROP TYPE statement fails with an error if you attempt to drop a built-in data type, such as the built-in opaque BOOLEAN or LVARCHAR type, or the built-in distinct IDSSECURITYLABEL type.

Do not confuse the DROP TYPE statement with the DROP ROW TYPE statement, which can only destroy named ROW types, including ROW types within data type hierarchies.

You cannot drop a distinct or opaque type if the database contains any casts, columns, or user-defined functions whose definitions reference the data type.

The following statement destroys the new_type data type: