DROP ROW TYPE statement

Use the DROP ROW TYPE statement to remove an existing named ROW data type from the database.

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

Syntax


Element	Description	Restrictions	Syntax
owner	Authorization identifier of the owner of row_type	Must own row_type	Owner name
row_type	Name of an existing named ROW data type to be dropped	Must exist. See also the Usage section that follows.	Identifier; Data Type

Usage

The DROP ROW TYPE statement removes the entry for the specified row_type from the sysxtdtypes system catalog table. You must be the owner of the specified named ROW data type or have the DBA privilege to execute the DROP ROW TYPE statement.

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

You cannot drop a named ROW data type if its name is in use in the database. The DROP ROW TYPE statement fails to destroy the specified ROW data type when any of the following conditions are true in the database:

Any existing tables or columns are using the named ROW data type.
The named ROW data type is a supertype in an inheritance hierarchy.
A view is defined on a column of the named ROW data type.

Example of dropping a named ROW type

If the named ROW type postal_t is registered in the system catalog of the database, but none of the restrictions listed above apply, then the following statement destroys the ROW data type whose name is postal_t:

DROP ROW TYPE postal_t RESTRICT;

If this DROP ROW TYPE statement succeeds, the row in the system catalog where the named ROW type postal_t is registered is deleted from the sysextdtypes table.

To drop a named ROW-type column from the schema of a table without removing the corresponding ROW type database object from the system catalog, use the ALTER TABLE . . . DROP COLUMN statement.

The DROP ROW TYPE statement cannot drop unnamed ROW data types.