Use 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.
Important: The order in which
you create named row types is important because a named row type must
exist before you can use it to define a typed 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.
Restriction: You cannot create a typed table
that is a temporary table.
For information about the advantages of using typed tables when you implement your data model, see Type inheritance.