Named Row Types

A named row type is a group of fields that are defined under a single name. A field refers to a component of a row type and should not be confused with a column, which is associated with tables only. The fields of a named row type are analogous to the fields of a C-language structure or members of a class in object-oriented programming. After you create a named row type, the name that you assign to the row type represents a unique type within the database. To create a named row type, you specify a name for the row type and the names and data types of its constituent fields. The following example shows how you might create a named row type called person_t:

CREATE ROW TYPE person_t
(
   name     VARCHAR(30) NOT NULL,
   address  VARCHAR(20),
   city     VARCHAR(20),
   state    CHAR(2),
   zip      VARCHAR(9),
   bdate    DATE
);

The person_t row type contains six fields: name, address, city, state, zip, and bdate. When you create a named row type, you can use it just as you would any other data type. The person_t can occur anywhere that you might use any other data type. The following CREATE TABLE statement uses the person_t data type:

CREATE TABLE sport_club
(
   sport     CHAR(20),
   sportnum  INT,
   member    person_t,
   since     DATE,
   paidup    BOOLEAN
)

You can use most data types to define the fields of a row type. For information about data types that are not supported in row types, see Restrictions on Named Row Types.

For the syntax you use to create a named row type, see the CREATE ROW TYPE statement in the IBM Informix: Guide to SQL Syntax. For information about how to cast row type values, see Creating and Using User-Defined Casts in Dynamic Server.

When to Use a Named Row Type

A named row type is one way to create a new data type in Dynamic Server. When you create a named row type, you are defining a template for fields of data types known to the database server. Thus the field definitions of a row type are analogous to the column definitions of a table: both are constructed from data types known to the database server.

You can create a named row type when you want a type that acts as a container for component values that users need to access. For example, you might create a named row type to support address values because users need direct access to the individual component values of an address such as street, city, state, and zip code. When you create the address type as a named row type, users always have direct access to each of the fields.

In contrast, if you create an opaque data type to handle address values, a C-language data structure stores all the address information. Because the component values of an opaque type are encapsulated, you would have to define functions to extract the component values for street, city, state, zip code. Thus, an opaque data type is a more complicated type to define and use.

Before you define a data type, determine whether the type is just a container for a group of values that users can access directly. If the type fits this description, use a named row type.

Choosing a Name for a Named Row Type

You can give a named row type any name that you like provided that the name does not violate the conventions established for the SQL identifiers. The conventions for SQL identifiers are described in the Identifier segment in the IBM Informix: Guide to SQL Syntax. To avoid confusing type and table names, the examples in this manual designate named row types with the _t characters at the end of the row type name.

You must have the Resource privilege to create a named row type. The name that you assign to a named row type should not be the same as any other data type that exists in the database because all data types share the same name space. In an ANSI-compliant database, the combination owner.type must be unique within the database. In a database that is not ANSI-compliant, the name must be unique within the database.

Restrictions on Named Row Types

This section describes the restrictions that apply when you use named row types.

Restrictions on Data Types

It is recommended that you use the BLOB or CLOB data types instead of the TEXT or BYTE data types when you create a typed table that contains columns for large objects. For backward compatibility, you can create a named row type that contains TEXT or BYTE fields and use that type to re-create an existing (untyped) table as a typed table. However, although you can use a named row type that contains TEXT or BYTE fields to create a typed table, you cannot use such a row type as a column. You can assign a named row type that contains BLOB or CLOB fields to a typed table or column.

Restrictions on Constraints

In a CREATE ROW TYPE statement, you can specify only the NOT NULL constraint for the fields of a named row type. You must define all other constraints in the CREATE TABLE statement. For more information, see the CREATE TABLE statement in the IBM Informix: Guide to SQL Syntax.

Restrictions on Indexes

You cannot use a CREATE INDEX statement to create an index on a named row type column. However, you can use a user-defined routine to create a functional index for a row type column.

Restrictions on SERIAL Data Types

A named row type that contains a SERIAL or SERIAL8 data type cannot be used as a column type in a table. The following statements return an error when the database server attempts to create the table:

CREATE ROW TYPE row_t (s_col SERIAL)

CREATE TABLE bad_tab (col1 row_t)

However, you can use a named row type that contains a SERIAL or SERIAL8 data type to create a typed table.

For information about the use and behavior of SERIAL and SERIAL8 types in table hierarchies, see SERIAL Types in a Table Hierarchy.

Using a Named Row Type to Create a Typed Table

You can create a table that is typed or untyped. A typed table is a table that has a named row type assigned to it. An untyped table is a table that does not have a named row type assigned to it. The CREATE ROW TYPE statement creates a named row type but does not allocate storage for instances of the row type. To allocate storage for instances of a named row type, you must assign the row type to a table. The following example shows how to create a typed table:

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

CREATE TABLE person OF TYPE person_t;

The first statement creates the person_t type. The second statement creates the person table, which contains instances of the person_t type. More specifically, each row in a typed table contains an instance of the named row type that is assigned to the table. In the preceding example, the fields of the person_t type define the columns of the person table.

Inserting data into a typed table is no different than inserting data into an untyped table. When you insert data into a typed table, the operation creates an instance of the row type and inserts it into the table. The following example shows how to insert a row into the person table:

INSERT INTO person 
VALUES ('Brown, James', '13 First St.', 'San Carlos', 'CA', 94070,
'01/04/1940')

The INSERT statement creates an instance of the person_t type and inserts it into the table. For more information about how to insert, update, and delete columns that are defined on named row types, see the IBM Informix: Guide to SQL Tutorial.

You can use a single named row type to create multiple typed tables. In this case, each table has a unique name, but all tables share the same type.

For information on the advantages of using typed tables when you implement your data model, see Type Inheritance.

Changing the Type of a Table

The primary advantage of typed tables over untyped tables is that typed tables can be used in an inheritance hierarchy. In general, inheritance allows a table to acquire the representation and behavior of another table. For more information, see What Is Inheritance?.

The DROP and ADD clauses of the ALTER TABLE statement let you change between typed and untyped tables. Neither the ADD nor DROP operation affects the data that is stored in the table.

Converting an Untyped Table into a Typed Table

If you want to convert an existing untyped table into a typed table, you can use the ALTER TABLE statement. For example, consider the following untyped table:

CREATE TABLE manager
(
   name        VARCHAR(30),
   department  VARCHAR(20),
   salary      INTEGER
);

To convert an untyped table to a typed table, both the field names and the field types of the named row type must match the column names and column types of the existing table. For example, to make the manager table a typed table, you must first create a named row type that matches the column definitions of the table. The following statement creates the manager_t type, which contains field names and field types that match the columns of the manager table:

CREATE ROW TYPE manager_t
(
   name        VARCHAR(30),
   department  VARCHAR(20),
   salary      INTEGER
);

After you create the named row type that you want to assign to the existing untyped table, use the ALTER TABLE statement to assign the type to the table. The following statement alters the manager table and makes it a typed table of type manager_t:

ALTER TABLE manager ADD TYPE manager_t

The new manager table contains the same columns and data types as the old table but now provides the advantages of a typed table.

Converting a Typed Table into an Untyped Table

You also use the ALTER TABLE statement to change a typed table into an untyped table:

ALTER TABLE manager DROP TYPE

Using a Named Row Type to Create a Column

Both typed and untyped tables can contain columns that are defined on named row types. A column that is defined on a named row type behaves in the same way whether the column occurs in a typed table or untyped table. In the following example, the first statement creates a named row type address_t; the second statement assigns the address_t type to the address column in the employee table:

CREATE ROW TYPE address_t
(
   street  VARCHAR(20),
   city    VARCHAR(20),
   state   CHAR(2),
   zip     VARCHAR(9)
);

CREATE TABLE employee
(
   name     VARCHAR(30),
   address  address_t,
   salary   INTEGER
);

In the preceding CREATE TABLE statement, the address column has the street, city, state, and zip fields of the address_t type. Consequently, the employee table, which has only three columns, contains values for name, street, city, state, zip, and salary. Use dot notation to access the individual fields of a column that are defined on a row type. For information about using dot notation to access fields of a column, see the IBM Informix: Guide to SQL Tutorial.

When you insert data into a column that is assigned a row type, you need to use the ROW constructor to specify row literal values for the row type. The following example shows how to use the INSERT statement to insert a row into the employee table:

INSERT INTO employee
VALUES ('John Bryant', 
  ROW('10 Bay Street', 'Madera', 'CA', 95400)::address_t, 55000);

Strong typing is not enforced for an insert or update on a named row type. To ensure that the row values are of the named row type, you must explicitly cast to the named row type to generate values of a named row type, as the previous example shows. The INSERT statement inserts three values, one of which is a row type value that contains four values. More specifically, the operation inserts unitary values for the name and salary columns but it creates an instance of the address_t type and inserts it into the address column.

For more information about how to insert, update, and delete columns that are defined on row types, see the IBM Informix: Guide to SQL Tutorial.

Using a Named Row Type Within Another Row Type

You can use a named row type as the data type of a field within another row type. A nested row type is a row type that contains another row type. You can nest any row type within any other row type. No practical limit exists on how deeply you can nest row types. However, to perform inserts or updates on deeply nested row types requires careful use of the syntax.

For named row types, the order in which you create the row types is important because a named row type must exist before you can use it to define a column or a field within another row type. In the following example, the first statement creates the address_t type, which is used in the second statement to define the type of the address field of the employee_t type:

CREATE ROW TYPE address_t
(
   street  VARCHAR (20),
   city    VARCHAR(20),
   state   CHAR(2),
   zip     VARCHAR(9)
);

CREATE ROW TYPE employee_t
(
   name     VARCHAR(30) NOT NULL,
   address  address_t,
   salary   INTEGER
);

Dropping Named Row Types

To drop a named row type, use the DROP ROW TYPE statement. You can drop a type only if it has no dependencies. You cannot drop a named row type if any of the following conditions are true:

• The type is currently assigned to a table.
• The type is currently assigned to a column in a table.
• The type is currently assigned to a field within another row type.

The following example shows how to drop the person_t type:

DROP ROW TYPE person_t restrict;

For information about how to drop a named row type from a type hierarchy, see Dropping Named Row Types from a Type Hierarchy.