ROW data type, Named

A named ROW data type must be declared with a name. This SQL identifier must be unique among data type names within the same database.

(An unnamed ROW type is a ROW type that contains fields but has no user-defined name.) Only named ROW types support data type inheritance. For more information, see ROW Data Types.

Defining named ROW types

You must declare and register in the database a new named ROW type by using the CREATE ROW TYPE statement of SQL. Definitions for named ROW types are stored in the sysxtdtypes system catalog table.

The fields of a ROW data type can be any built-in data type or UDT, but TEXT or BYTE fields of a ROW type are valid in typed tables only. If you want to assign a ROW type to a column in the CREATE TABLE or ALTER TABLE statements, its elements cannot be TEXT or BYTE data types.

In general, the data type of a field of a ROW type can be any of these types:
  • A built-in type (except for the TEXT or BYTE data types)
  • A collection type (LIST, MULTISET, or SET)
  • A distinct type
  • Another named or unnamed ROW type
  • An opaque type
These SQL statements maintain the definitions of named ROW data types:
  • The CREATE ROW TYPE statement adds a named ROW type to the database.
  • The DROP ROW TYPE statement removes a previously defined named ROW type from the database.

No more than 195 columns of the same table can be named ROW types.

For details about these SQL syntax statements, see the HCL OneDB™ Guide to SQL: Syntax. For examples of how to create and use named ROW types, see the HCL OneDB Database Design and Implementation Guide.

Equivalence and named ROW types

No two named ROW types can be equal, even if they have identical structures, because they have different names. For example, the following named ROW types have the same structure (the same number of fields and the same order of data types of fields within the row) but they are not equal:
name_t (lname CHAR(15), initial CHAR(1), fname CHAR(15))
emp_t (lname CHAR(15), initial CHAR(1), fname CHAR(15))
A Boolean equality condition like name_t = emp_t always evaluates to FALSE if both of the operands are different named ROW types.

Named ROW types and inheritance

Named ROW types can be part of a type-inheritance hierarchy. One named ROW type can be the parent (or supertype) of another named ROW type. A subtype in a hierarchy inherits all the properties of its supertype. Type inheritance is explained in the CREATE ROW TYPE statement in the HCL OneDB Guide to SQL: Syntax and in the HCL OneDB Database Design and Implementation Guide.

Typed tables

Tables that are part of an inheritance hierarchy must be typed tables. Typed tables are tables that have been assigned a named ROW type. For the syntax you use to create typed tables, see the CREATE TABLE statement in the HCL OneDB Guide to SQL: Syntax. Table inheritance and its relation to type inheritance is also explained in that section. For information about how to create and use typed tables, see the HCL OneDB Database Design and Implementation Guide.