OF TYPE Clause

Use the OF TYPE clause to create a typed table for an object-relational database. A typed table is a table in which every row is an object of the named ROW data type that you specify in this clause.

This syntax fragment is part of the CREATE TABLE statement.

OF TYPE Clause

Notes:

¹ See Multiple-Column Constraint Format


Element	Description	Restrictions	Syntax
row_type	Name of the ROW type on which this table is based	Must be a named ROW data type registered in the local database	Identifier
supertable	Name of the table from which this table inherits its properties	Must already exist as a typed table	Identifier

If you use the UNDER clause, the row_type must be derived from the ROW type of the supertable. A type hierarchy must already exist in which the named ROW type of the new table is a subtype of the named ROW type of the supertable.

Jagged rows are any set rows from a table hierarchy in which the number of columns is not fixed among the typed tables within the hierarchy. Some APIs, such as and OneDB® JDBC Driver, do not support queries that return jagged rows.

When you create a typed table, CREATE TABLE cannot specify names for its columns, because the column names were declared when you created the ROW type. Columns of a typed table correspond to the fields of the named ROW type. The ALTER TABLE statement cannot add additional columns to a typed table.

For example, suppose you create a named ROW type, student_t, as follows:

CREATE ROW TYPE student_t
   (name        VARCHAR(30),
    average     REAL,
    birthdate   DATETIME YEAR TO DAY);

If a table is assigned the type student_t in the OF TYPE clause, the table is a typed table whose columns are of the same name and data type, and in the same order, as the fields of the named ROW type student_t. For example, the following CREATE TABLE statement creates a typed table named students whose type is student_t:

CREATE TABLE students OF TYPE student_t;

The students table has the following columns:

name      VARCHAR(30)
average   REAL
birthdate DATETIME YEAR TO DAY

For more information about named ROW types, refer to the CREATE ROW TYPE statement.