ADD TYPE Clause

Use the ADD TYPE clause to convert a table that is not based on a named ROW data type into a typed table.

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

ADD TYPE Clause


Element	Description	Restrictions	Syntax
row_type	Identifier of an existing named ROW data type for this table	The row_type fields must exactly match the existing columns in their data types, order, and number	Identifier
`synonym`	Synonym for the table that becomes a typed table	Synonym and its table must exist, and USETABLENAME must not be set	Identifier
`table`	Identifier of the table that becomes a typed table	Must exist in the current database	Identifier

Usage

If the new ROW type is a subtype of an existing named ROW type, you must hold the UNDER privilege for that named ROW type.

When you use the ADD TYPE clause, you assign the specified named ROW data type to a table whose columns must exactly match the fields of that ROW data type

in their names,
in their individual data types,
and in their order.

In addition to the requirements common to all ALTER TABLE operations (namely DBA privilege on the database, Alter privilege on the table, and ownership of the table), all of the following must be also true when you use the ADD TYPE clause to convert an untyped table to the specified named ROW data type:

The named ROW data type is already registered in the database.
You hold the Usage privilege on the named ROW data type.
There must be a 1-to-1 correspondence between the ordered set of column data types of the untyped table and the ordered set of field data types of the named ROW data type.
The table cannot be a fragmented table that has rowid values.

You cannot combine the ADD TYPE clause with any clause that changes the schema of the table. No other ADD, DROP, or MODIFY clause is valid in the same ALTER TABLE statement that has the ADD TYPE clause. The ADD TYPE clause does not allow you to change column data types. (To change the data type of a column, use the MODIFY clause in a separate ALTER TABLE statement from the ADD TYPE operation.)

ALTER TABLE operations on typed tables

Most options of the ALTER TABLE statement are restricted to untyped tables.

The database server supports only the following storage, locking, and constraint syntax options when table_object is the name or synonym of a typed table. As noted below, however, for some CONSTRAINT options in a typed-table inheritance hierarchy

the scope of the operation includes all subtables of table_object,
or the operation is not supported if table_object is a subtable.

Figure 1: ALTER TABLE options for typed tables

Typed-Table options

Notes:

¹ See ADD CONSTRAINT Clause
² See DROP CONSTRAINT Clause
³ Use path no more than once
⁴ See MODIFY NEXT SIZE clause
⁵ See LOCK MODE Clause

Two additional considerations apply to typed tables of named ROW types that are part of inheritance hierarchies:

For subtables, ADD CONSTRAINT and DROP CONSTRAINT are not valid on inherited constraints.
For supertables, ADD CONSTRAINT and DROP CONSTRAINT propagate to all subtables.

Example of the ALTER TABLE . . . ADD TYPE statement

The following example changes an untyped table into a typed table by the following steps:

Use the CREATE TABLE statement to create an table that will become the typed table.
Use the CREATE ROW TYPE statement to create a named ROW type with fields exactly matching the untyped table schema.
Use the ALTER TABLE . . . ADD TYPE statement to change the untyped table to a typed table.

CREATE TABLE postal(
   name     VARCHAR(30),
   address  VARCHAR(20),
   city     VARCHAR(20),
   state    CHAR(2),
   zip      INTEGER,
);

. . . 

CREATE ROW TYPE postal_t
(
   name     VARCHAR(30),
   address  VARCHAR(20),
   city     VARCHAR(20),
   state    CHAR(2),
   zip      INTEGER,
);

. . . 

ALTER TABLE postal ADD TYPE postal_t;

You could achieve the same result if the order of the first two DDL statements were reversed.

In that case, however, the ALTER TABLE statement could be avoided, if instead of defining columns individually, the CREATE TABLE instead included the OF TYPE clause to use the postal_t ROW type as a template for the six fields of a single named ROW type column:

CREATE TABLE postal OF TYPE postal_t;

By either path, table postal becomes a typed table of ROW type postal_t.

In the example above, as elsewhere in the documentation of typed tables, the database server does not require any similarity between the SQL identifier of the table and the SQL identifier of the ROW type. Table objects and named ROW data types have separate and uncorrelated name spaces, but mnenomic names can make SQL code examples easier for some human readers to understand.