CREATE ROW TYPE statement

Use the CREATE ROW TYPE statement to create a named ROW type.

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

Syntax

Named ROW type with no parent

Named child ROW type

Field Definition


Element	Description	Restrictions	Syntax
data_type	Data type of the field	See Restrictions on Serial and Simple-Large-Object Data Types.	Identifier
field	Name of a field in row_type	Must be unique among field names of this ROW type and of its supertype	Identifier
owner	Authorization identifier of the owner of this ROW type	In an ANSI-compliant database, the combination owner.row_type must be unique among data-type objects	Owner name
row_type	Name declared here for a new named ROW data type	See Procedure for Creating a Subtype. Must be unique among data type names in the database.	Identifier
supertype	Name of the supertype of row_type within a data-type inheritance hierarchy	To create a child ROW type, this must exist in the database as a named ROW type, all of whose fields this row_type inherits	Data Type

Usage

You must hold the Resource privilege to use this statement. If the UNDER clause declares the new ROW type as a subtype of an existing named ROW type, you must also hold the UNDER privilege for that named ROW type. You are, by default, the owner of the named ROW types that you create, but a DBA who issues the CREATE ROW TYPE statement can designate another user as owner.

The CREATE ROW TYPE statement declares a named ROW data type and registers it in the sysxtdtypes system catalog table. You can assign a named ROW data type to a table or view to create a typed table or typed view. You can also define a column as a named ROW type.

The following example creates a named ROW type called people_t with four fields:

CREATE ROW TYPE people_t
(
   name     VARCHAR(40) NOT NULL,
   address  VARCHAR(35),
   city     VARCHAR(25),
   bdate    DATE
);

In some SQL code examples in this document, the SQL identifiers of named ROW types have _t as the last two characters. This suffix is only a documentation convention, and not a requirement of the database server.

Although you can assign a ROW type to a table to define the schema of the table, ROW data types are not the same as table rows. Table rows consist of one or more columns; ROW data types consist of one or more fields, defined using the Field Definition syntax.

A named ROW data type is valid in most contexts where you can specify a data type.

The following CREATE TABLE statement defines a typed table whose only column is of the people_t ROW type:

CREATE TABLE birthdays OF TYPE people_t
   LOCK MODE ROW;

Named ROW types are said to be strongly typed. No two named ROW types are equivalent, even if they are structurally equivalent. To compare values of a named ROW type with values of another named ROW type, or with values of an unnamed ROW type, you must use an explicit cast, so that both rows are of the same data type.

ROW types without identifiers are called unnamed ROW types. Any two unnamed ROW types are considered equivalent if they are structurally equivalent. For more information, see ROW Data Types.

Discretionary access privileges on the fields of named ROW types are the same as privileges on columns. For more information, see Table-Level Privileges. (To see what privileges you have on a column, check the syscolauth system catalog table, which is described in the HCL OneDB™ Guide to SQL: Reference.)

If you include the optional IF NOT EXISTS keywords, the database server takes no action, rather than sending an exception to the application, if a named ROW of the specified name is already registered in the current database. In this case, the CREATE ROW TYPE statement has no effect, and does not register a new named ROW type in the sysxtdtypes system catalog table.

If no field is of the SERIAL, BIGSERIAL, or SERIAL8 data type, named ROW data types that the CREATE ROW TYPE statement defines can be the source type for DISTINCT ROW data types that the CREATE DISTINCT TYPE statement can define.

Important:

You cannot use the CREATE ROW TYPE statement to create an unnamed ROW type. Unnamed ROW types are created in the CREATE TABLE statement or in the ALTER TABLE statement by including the ROW constructor syntax segment in the definition of a column. This can be an unnamed ROW type column that you declare in the CREATE TABLE statement, or an unnamed ROW type that you add to the schema of an existing table by using the ALTER TABLE statement.

Field definition

When you define a new named ROW type with no UNDER clause, the Field Definition clause defines an ordered list of the data types and NOT NULL constraints of one or more fields in the new named ROW type.

If the UNDER clause identifies a supertype, however, the Field Definition clause can append one or more fields to the ordered list of fields that the new child ROW type inherits from its parent supertype within a named ROW type hierarchy.

If you omit the Field definition, the new subtype has only the fields that it inherits from the parent supertype that the UNDER clause specifies.

The NOT NULL constraint on the named ROW type field applies to the corresponding columns when a typed table of the named ROW type is created. If you omit the NOT NULL keywords, then by default, the field accepts NULL values.

The NOT NULL constraint is the only constraint that the CREATE ROW TYPE statement can define for a field of a named ROW type. To define any other constraints on the fields of a column of a named ROW type, you must use the CREATE TABLE statement, or in the ALTER TABLE statement.

The HCL OneDB implementation of the SQL language supports no ALTER ROW TYPE statement for changing the definition of an existing named ROW type. For named ROW types that are not currently instantiated by any existing table, column, view, or named ROW type hierarchy in the database, you can use the DROP ROW TYPE statement to remove an existing named ROW type from the system catalog. You can then use the CREATE ROW TYPE statement to define a replacement named ROW type.

See, however, the DROP ROW TYPE statement for restrictions on dropping an existing named ROW type.