Informix Guide to SQL: Syntax SQL Statements

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

The CREATE ROW TYPE statement creates a named-row type. You can assign a named-row type to a table or view to create a typed table or typed view. You can also assign a named-row type to a column. Although you can assign a row type to a table to define the structure of the table, row types are not the same as table rows. Table rows consist of one or more columns; row types consist of one or more fields, which are defined using the Field Definition syntax.

You can use a named-row type anywhere you can use any other data type. Named-row types are strongly typed. Any two named-row types are not considered equivalent even if they are structurally equivalent. Row types without names are called unnamed-row types. Any two unnamed-row types are considered equivalent if they are structurally equivalent. For more information on unnamed-row types, see Unnamed Row Types.

The following table indicates which privileges you must have to create a row type.

To find out what privileges exist on a particular type, check the sysxtdtypes system catalog table for the owner name and the sysxtdtypeauth system catalog table for additional type privileges that might have been granted. For more information on system catalog tables, see the Informix Guide to SQL: Reference.

For information about the RESOURCE, UNDER, and ALL privileges, see GRANT.

Privileges on a typed table (a table that is assigned a named-row type) are the same as privileges on any table. For more information, see Table-Level Privileges.

To find out what privileges you have on a particular table, check the systabauth system catalog table.

Privileges on named-row type columns are the same as privileges on any column. For more information, see Table-Level Privileges.

To find out what privileges you have on a particular column, check the syscolauth system catalog table. This table is described in the Informix Guide to SQL: Reference.

A named-row type can belong to an inheritance hierarchy, as either a subtype or a supertype. You use the UNDER clause in the CREATE ROW TYPE statement to create a named-row type as a subtype. The supertype must also be a named-row type.

When you create a named-row type as a subtype, the subtype inherits all fields of the supertype. In addition, you can add new fields to the subtype that you create. The new fields are specific to the subtype alone.

You cannot substitute a row type in an inheritance hierarchy for its supertype or its subtype. For example, suppose you define a type hierarchy in which person_t is the supertype and employee_t is the subtype. If a column is of type person_t, the column can only contain person_t data. It cannot contain employee_t data. Likewise, if a column is of type employee_t, the column can only contain employee_t data. It cannot contain person_t data.

In most cases, you add new fields when you create a named-row type as a subtype of another named-row type (supertype). To create the fields of a named-row type, you use the field definition clause (see Field Definition).

When you create a subtype, you must use the UNDER keyword to associate the supertype with the named-row type that you want to create. The following statement creates the employee_t type under the person_t type:

CREATE ROW TYPE employee_t 

(salary NUMERIC(10,2), bonus NUMERIC(10,2))

UNDER person_t; 

The employee_t type inherits all the fields of person_t and has two additional fields: salary and bonus. However, the person_t type is not altered.

When you create a subtype, you create a type hierarchy. In a type hierarchy, each subtype that you create inherits its properties from a single supertype. If you create a named-row type customer_t under person_t, customer_t inherits all the fields of person_t. If you create another named-row type, salesrep_t under customer_t, salesrep_t inherits all the fields of customer_t. More specifically, salesrep_t inherits all the fields that customer_t inherited from person_t as well as all the fields defined specifically for customer_t. For a full discussion of type inheritance, refer to the Informix Guide to SQL: Tutorial.

Before you create a named-row type as a subtype in an inheritance hierarchy, check the following information:

• Verify that you are authorized to create new data types.

You must have the Resource privilege on the database. You can find this information in the sysusers system catalog table.

• Verify that the supertype exists.

You can find this information in the sysxtdtypes system catalog table.

• Verify that you are authorized to create subtypes to that supertype.

You must have the Under privilege on the supertype.You can find this information in the sysusers system catalog table.

• Verify that the name that you assign to the named-row type is unique within the schema.

To verify whether the name you want to assign to a new data type is unique within the schema, check the sysxtdtypes system catalog table. The name you want to use must not be the name of an existing data type.

• If you are defining fields for the row type, check that no duplicate field names exist in both new and inherited fields.

Important: When you create a subtype, do not redefine fields that the subtype inherited for its supertype. If you attempt to redefine these fields, the database server returns an error.

You cannot apply constraints to named-row types directly. Specify the constraints for the tables that use named-row types when you create or alter the table.

Use the field definition portion of CREATE ROW TYPE to define a new field in a named-row type.

The NOT NULL constraint that you specify on a field of a named-row type also applies to corresponding columns of a table when the named-row type is used to create a typed table.

Serial and simple-large-object data types cannot be nested within a table. Therefore, if a row type contains a BYTE, TEXT, SERIAL, or SERIAL8 field, you cannot use the row type to define a column in a table that is not based on a row type. For example, the following code example produces an error:

CREATE ROW TYPE serialtype (s serial, s8 serial8);
CREATE TABLE tab1 (col1 serialtype) --INVALID CODE

You cannot create a row type that has a BYTE or TEXT value that is stored in a separate storage space. That is, you cannot use the IN clause to specify the storage location. For example, the following example produces an error:

CREATE ROW TYPE row1 (field1 byte IN blobspace1) --INVALID 
CODE

Across a table hierarchy, you can use only one SERIAL and one SERIAL8. That is, if a supertable table contains a SERIAL column, no subtable can contain a SERIAL column. However, a subtable can have a SERIAL8 column (as long as no other subtables contain a SERIAL8 column). Consequently, when you create the named-row types on which the table hierarchy is to be based, they can contain at most one SERIAL and one SERIAL8 field among them.

You cannot set the starting serial value with CREATE ROW TYPE.

To modify the value for a serial field, you must use either the MODIFY clause of the ALTER TABLE statement or the INSERT statement to insert a value that is larger than the current maximum (or default) serial value.

When you use serial fields in row types, you create performance implications across a table hierarchy. When you insert data into a subtable whose supertable (or its supertable) contains the serial counter, the database server must also open the supertable, update the serial value, and close the supertable, thus adding extra overhead.

Related statements: DROP ROW TYPE, CREATE TABLE, CREATE CAST, GRANT, and REVOKE

For a discussion of named-row types, see the Informix Guide to Database Design and Implementation and the Informix Guide to SQL: Reference.