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.

Syntax


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

Usage

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:

DROP TYPE new_type RESTRICT;