Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 10: Understanding Complex Data Types

Home Contents Index Master Index New Book

What Is Inheritance?

Inheritance is the process that allows a type or a table to acquire the properties of another type or table. The type or table that inherits the properties is called the subtype or subtable. The type or table whose properties are inherited is called the supertype or supertable. Inheritance allows for incremental modification, so that a type or table can inherit a general set of properties and add properties that are specific to itself. You can use inheritance to make modifications only to the extent that the modifications do not alter the inherited supertypes or supertables.

Universal Server supports inheritance only for named row types and typed tables. Universal Server supports only single inheritance. With single inheritance, each subtype or subtable has only one supertype or supertable.

Type Inheritance

Type inheritance applies to named row types only. You can use inheritance to group named row types into a type hierarchy in which each subtype inherits the representation (data fields) and the behavior (routines, aggregates, and operators) of the supertype under which it is defined. A type hierarchy provides the following advantages:

It encourages modular implementation of your data model.
It ensures consistent reuse of schema components.
It ensures that no data fields are accidentally left out.
It allows a type to inherit routines that are defined on another type.

Defining a Type Hierarchy

Figure 10-2 provides an example of a simple type hierarchy that contains three named row types.


	Figure 10-2 Example of a Type Hierarchy

The supertype at the top of the type hierarchy contains a group of fields that all underlying subtypes inherit. A supertype must exist before you can create its subtype. The following example creates the person_t supertype of the type hierarchy that Figure 10-2 shows:

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

To create a subtype, specify the UNDER keyword and the name of the supertype whose properties the subtype inherits. The following example illustrates how you might define employee_t as a subtype that inherits all the fields of person_t. The example adds salary and manager fields that do not exist in the person_t type.

CREATE ROW TYPE employee_t 
(
	salary		INTEGER,
	manager		VARCHAR(30)
) 
UNDER person_t;

Important: You must have the UNDER privilege on the supertype before you can create a subtype that inherits the properties of the supertype. For information about UNDER privileges, see Chapter 11, "Granting and Limiting Access to Your Database."

In the type hierarchy of Figure 10-2, sales_rep_t is a subtype of employee_t, which is the supertype of sales_rep_t in the same way that person_t is the supertype of employee_t. The following example creates sales_rep_t, which inherits all fields from person_t and employee_t and adds four new fields. Because the modifications on a subtype do not affect its supertype, employee_t does not have the four fields that are added for sales_rep_t.

CREATE ROW TYPE sales_rep_t 
(
rep_num			INT8,
region_num			INTEGER,
commission			DECIMAL,
home_office			BOOLEAN
) 
UNDER employee_t;

The sales_rep_t type contains 12 fields: name, address, city, state, zip, bdate, salary, manager, rep_num, region_num, commission, and home_office.

Instances of both the employee_t and sales_rep_t types inherit all the routines that are defined for the person_t type. Any additional routines that are defined on employee_t automatically apply to instances of the employee_t type and to instances of its subtype sales_rep_t, but not to instances of person_t.

The preceding type hierarchy is an example of single inheritance because each subtype inherits from a single supertype. Figure 10-3 illustrates how you can define multiple subtypes under a single supertype. Although single inheritance requires that every subtype inherits from one and only one supertype, there is no practical limit on the depth or breadth of the type hierarchy that you define.

Figure 10-3
Example of a Type Hierarchy That Is a Tree Structure

The topmost type of any hierarchy is referred to as the root supertype. In Figure 10-3, person_t is the root supertype of the hierarchy. Except for the root supertype, any type in the hierarchy can be potentially both a supertype and subtype at the same time. For example, customer_t is a subtype of person_t and a supertype of us_customer_t. A subtype at the lower levels of the hierarchy contains properties of the root supertype but does not directly inherit its properties from the root supertype. For example, us_customer_t has only one supertype, customer_t, but because customer_t is itself a subtype of person_t, the fields and routines that customer_t inherits from person_t are also inherited by us_customer_t.

Overloading Routines for Types in a Type Hierarchy

Routine overloading refers to the ability to assign one name to multiple routines and specify different types of arguments on which the routines can operate. In a type hierarchy, a subtype automatically inherits the routines that are defined on its supertype. However you can define a new routine on a subtype to override the inherited routine with the same name. For example, suppose you create a getinfo() routine on type person_t that returns the last name and birthdate of an instance of type person_t. You can register another getinfo() routine on type employee_t that returns the last name and salary from an instance of employee_t. In this way, you can overload a routine, so that you have a customized routine for every type in the type hierarchy, as Figure 10-4 shows.


Figure 10-4 Example of Routine Overloading in a Type Hierarchy

When you overload a routine so that routines are defined with the same name but different arguments for different types in the type hierarchy, the argument that you specify determines which routine executes. For example, if you call getinfo() with an argument of type employee_t, a getinfo() routine defined on type employee_t overrides the inherited routine of the same name. Similarly, if you define another getinfo() on type sales_rep_t, a call to getinfo() with an argument of type sales_rep_t overrides the routine that sales_rep_t inherits from employee_t.

For information about how to create and register external routines, see Chapter 13, "Casting Data Types." For information about how to create and register routines in Stored Procedure Language (SPL), see Chapter 14, "Creating and Using SPL Routines."

Inheritance and Type Substitutability

In a type hierarchy, a subtype automatically inherits all the routines defined on its supertype. Consequently, if you call a routine with an argument of a subtype and no routines are defined on the subtype, the database server can invoke a routine that is defined on a supertype. Type substitutability refers to the ability to use an instance of a subtype when an instance of a supertype is expected. As an example, suppose that you create a routine p_info() that accepts an argument of type person_t and returns the last name and birthdate of an instance of type person_t. If no other p_info() routines are registered, and you invoke p_info() with an argument of type employee_t, the routine returns the name and birthdate fields (inherited from person_t) from an instance of type employee_t. This behavior is possible because employee_t inherits the functions of its supertype, person_t.

In general, when the database server attempts to evaluate a routine, the database server searches for a signature that matches the routine name and the arguments that you specify when you invoke the routine. If such a routine is found, then the database server uses this routine. If an exact match is not found, the database server attempts to find a routine with the same name and whose argument type is a supertype of the argument type that is specified when the routine is invoked. Figure 10-5 shows how the database server searches for a routine that it can use when a get() routine is called with an argument of the subtype sales_rep_t. Although no get() routine has been defined on the sales_rep_t type, the database server searches for a routine until it finds a get() routine that has been defined on a supertype in the hierarchy. In this case, neither sales_rep_t nor its supertype employee_t has a get() routine defined over it. However, because a routine is defined for person_t, this routine is invoked to operate on an instance of sales_rep_t.

Figure 10-5
Example of How the Database Server Searches for a Routine in a Type Hierarchy

The process in which the database server searches for a routine that it can use is called routine resolution. For more information about routine resolution, see Extending INFORMIX-Universal Server: User-Defined Routines.

Dropping Named Row Types from a Type Hierarchy

To drop a named row type from a type hierarchy, use the DROP ROW TYPE statement. However, you can drop a type only if it has no dependencies. You cannot drop a named row type if either of the following conditions is true:

The type is currently assigned to a table.
The type is a supertype of another type.

The following example shows how to drop the sales_rep_t type:

DROP ROW TYPE games_t restrict;

To drop a supertype, you must first drop each subtype that inherits properties from the supertype. You drop types in a type hierarchy in the reverse order in which you create the types. For example, to drop the person_t type shown in Figure 10-5, you must first drop its subtypes in the following order:

DROP ROW TYPE sale_rep_t restrict;
DROP ROW TYPE employee_t restrict;
DROP ROW TYPE person_t restrict;

Important: To drop a type, you must be the database administrator or the owner of the type.

Restrictions on Type Hierarchies

If a column is defined on a named row type, the column cannot contain an instance of any type other than the type on which column is defined. For example, a column of type address_t can only contain instances of type address_t.

A named row type cannot contain a column that is defined on the SERIAL or SERIAL8 data types. Consequently, the types that define a type hierarchy cannot contain fields that are defined on the SERIAL or SERIAL8 data type.

Table Inheritance

Only tables that are defined on named row types support table inheritance. Table inheritance is the property that allows a table to inherit the behavior (constraints, storage options, triggers) from the supertable above it in the table hierarchy. A table hierarchy is the relationship that you can define among tables in which subtables inherit the behavior of supertables. A table inheritance provides the following advantages:

It encourages modular implementation of your data model.
It ensures consistent reuse of schema components.
It allows you to construct queries whose scope can be some or all of the tables in the table hierarchy.

In a table hierarchy, a subtable automatically inherits the following properties from its supertable:

All constraint definitions (primary key, unique, and referential constraints)
Storage option
All triggers
Indexes
Access method

Important: Typed tables do not support rowids. Therefore you cannot specify the WITH ROWID or ADD ROWID clauses when you create tables in a table hierarchy.

The Relationship Between Type and Table Hierarchies

Every table in a table hierarchy must be assigned to a named row type in a corresponding type hierarchy. Figure 10-6 shows an example of the relationships that can exist between a type hierarchy and table hierarchy.


Figure 10-6 Example of the Relationship Between Type Hierarchy and Table Hierarchy

However, you also can define a type hierarchy in which the named row types do not necessarily have a one-to-one correspondence with the tables in a table hierarchy. Figure 10-7 shows how you might create a type hierarchy for which only some of the named row types have been assigned to tables.


Figure 10-7 Example of an Inheritance Hierarchy in Which Only Some Types Have Been Assigned to Tables

Defining a Table Hierarchy

The type that you use to define a table must exist before you can create the table. Similarly, you define a type hierarchy before you define a corresponding table hierarchy. To establish the relationships between specific subtables and supertables in a table hierarchy, use the UNDER keyword. The following CREATE TABLE statements define the simple table hierarchy that Figure 10-6 shows. The examples in this section assume that the person_t, employee_t, and sales_rep_t types already exist.

CREATE TABLE person OF TYPE person_t;



CREATE TABLE employee OF TYPE employee_t
UNDER person;


CREATE TABLE sales_rep OF TYPE sales_rep_t
UNDER employee;

The person, employee, and sales_rep tables are defined on the person_t, employee_t, and sales_rep_t types, respectively. Thus, for every type in the type hierarchy, a corresponding table exists in the table hierarchy. In addition, the relationship between the tables of a table hierarchy must match the relationship between the types of the type hierarchy. For example, the employee table inherits from person table in the same way that the employee_t type inherits from the person_t type, and the sales_rep table inherits from the employee table in the same way that the sales_rep_t type inherits from the employee_t type.

Subtables automatically inherit all inheritable properties that are added to supertables. Therefore, you can add or alter the properties of a supertable at any time and the subtables automatically inherit the changes. For more information, see "Modifying Table Behavior in a Table Hierarchy".

Important: You must have the UNDER privilege on the supertable before you can create a subtable that inherits the properties of the supertable. For information about UNDER privileges, see "Table-Level Privileges".

Inheritance of Table Behavior in a Table Hierarchy

When you create a subtable under a supertable, the subtable inherits all the properties of its supertable, including the following ones:

All columns of the supertable.
Constraint definitions.
Storage options.
Indexes.
Referential integrity.
Triggers.
The access method.

In addition, if table c inherits from table b and table b inherits from table a, then table c automatically inherits the behavior unique to table b as well as the behavior that table b has inherited from table a. Consequently, the supertable that actually defines behavior can be several levels distant from the subtables that inherit the behavior. For example, consider the following table hierarchy:

CREATE TABLE person OF TYPE person_t
(PRIMARY KEY (name))
FRAGMENT BY EXPRESSION
name < 'n' IN dbspace1,
name >= 'n' IN dbspace2;



CREATE TABLE employee OF TYPE employee_t
(CHECK(salary > 34000)) 
UNDER person;


CREATE TABLE sales_rep OF TYPE sales_rep_t
LOCK MODE ROW
UNDER employee;

In this table hierarchy, the employee and sales_rep tables inherit the primary key name and fragmentation strategy of the person table. The sales_rep table inherits the check constraint of the employee table and adds a LOCK MODE. The following table shows the behavior for each table in the hierarchy.


Table	Table Behavior
person	`PRIMARY KEY, FRAGMENT BY EXPRESSION`
employee	`PRIMARY KEY, FRAGMENT BY EXPRESSION, CHECK` constraint
sales_rep	`PRIMARY KEY, FRAGMENT BY EXPRESSION, CHECK` constraint`, LOCK MODE ROW`

A table hierarchy might also contain subtables in which behavior defined on a subtable can override behavior (otherwise) inherited from its supertable. Consider the following table hierarchy, which is identical to the previous example except that the employee table adds a new storage option:

CREATE TABLE person OF TYPE person_t
(PRIMARY KEY (name))
FRAGMENT BY EXPRESSION
name < 'n' IN person1,
name>= 'n' IN person2;



CREATE TABLE employee OF TYPE employee_t
(CHECK(salary > 34000))
FRAGMENT BY EXPRESSION
name < 'n' IN employ1,
name>= 'n' IN employ2

UNDER person;


CREATE TABLE sales_rep OF TYPE sales_rep_t
LOCK MODE ROW
UNDER employee;

Again, the employee and sales_rep tables inherit the primary key name of the person table. However, the fragmentation strategy of the employee table overrides the fragmentation strategy of the person table. Consequently, both the employee and sales_rep tables store data in dbspaces employ1 and employ2, whereas the person table stores data in dbspaces person1 and person2.

Modifying Table Behavior in a Table Hierarchy

Once you define a table hierarchy, you cannot modify the structure (columns) of the existing tables. However, you can modify the behavior of tables in the hierarchy. Figure 10-8 shows the table behavior that you can modify in a table hierarchy and the syntax that you use to make modifications.

Figure 10-8
Table Behavior That You Can Modify in a Table Hierarchy

Table Behavior Syntax Considerations
Constraint definitions
ALTER TABLE
To add or drop a constraint, use the ADD CONSTRAINT or DROP CONSTRAINT clause. For information about constraints on tables in a table hierarchy, see "Constraints on Tables in a Table Hierarchy."

Indexes
CREATE INDEX, ALTER INDEX
For information about indexes on tables in a table hierarchy, see "Adding Indexes to Tables in a Table Hierarchy." For information about how to create or alter an index on a table, see the CREATE INDEX or ALTER INDEX statements in the Informix Guide to SQL: Syntax.

Triggers
CREATE/DROP TRIGGER
You cannot drop an inherited trigger. However, you can drop a trigger from a supertable or override an inherited trigger by adding a trigger to a subtable. For information about modifying triggers on supertables and subtables, see "Triggers on Tables in a Table Hierarchy". For information about how to create a trigger, see Chapter 15, "Creating and Using Triggers."

All existing subtables automatically inherit new table behavior when you modify a supertable in the hierarchy.

Important: When you use the ALTER TABLE statement to modify a table in a table hierarch, you can use only the ADD CONSTRAINT, DROP CONSTRAINT,
MODIFY NEXT SIZE, and LOCK MODE clauses.

Constraints on Tables in a Table Hierarchy

You can alter or drop a constraint only in the table on which it is defined. You cannot drop or alter a constraint from a subtable when the constraint is inherited. However, a subtable can add additional constraints. Any additional constraints that you define on a table are also inherited by any subtables that inherit from the table that defines the constraint. Because constraints are additive, all inherited and current (added) constraints apply.

Adding Indexes to Tables in a Table Hierarchy

An index that a subtable inherits from a supertable cannot be dropped or modified. However, you can add indexes to a subtable. Indexes, unique constraints, and primary keys are all closely related. (When you specify a unique constraint or primary key, the database server automatically creates a unique index on the column). A primary key or unique constraint that you define on a supertable applies to all the subtables. For example, suppose there are two tables (a supertable and subtable), both of which contain a column emp_id. If the supertable specifies that emp_id has a unique constraint, the subtable must contain emp_id values that are unique across both the subtable and the supertable.

Important: You cannot define more than one primary key across a table hierarchy, even if some of the tables in the hierarchy do not inherit the primary key.

Triggers on Tables in a Table Hierarchy

You cannot drop an inherited trigger. However, you can add a trigger to a subtable that overrides the trigger that the subtable inherits from a supertable. Unlike constraints, triggers are not additive; only the nearest trigger on a supertable in the hierarchy applies. If you want to disable the trigger that a subtable inherits from its supertable, you can create an empty trigger on the subtable that has the same name as the trigger from the supertable. Because triggers are not additive, this empty trigger executes for the subtable (and any subtables under the subtable, which are not subject to further overrides).

Adding a New Table to a Table Hierarchy

Once you define a table hierarchy, you cannot use the ALTER TABLE statement to add, drop, or modify columns of a table within the hierarchy. However, you can add new subtypes and subtables to an existing inheritance hierarchy provided that the new subtype and subtable do not interfere with existing inheritance relationships. Figure 10-9 illustrates one way that you might add a type and corresponding table to an existing inheritance hierarchy. The dashed lines indicate the added subtype and subtable.


Figure 10-9 Example of How You Might Add a Subtype and Subtable to an Existing Inheritance Hierarchy

The following statements show how you might add the type and table to the inheritance hierarchy shown in Figure 10-9:

CREATE ROW TYPE us_sales_rep_t
(
domestic_sales				DECIMAL(15,2)
) 
UNDER employee_t;


CREATE TABLE us_sales_rep OF TYPE us_sales_rep_t
UNDER sales_rep;

You can also add subtypes and subtables that branch from an existing supertype and its parallel supertable. Figure 10-10 shows how you might add the customer_t type and customer table to existing hierarchies. In this example, both the customer table and the employee table inherit properties from the person table.


Figure 10-10 Example of Adding a Type and Table Under an Existing Supertype and Supertable

The following statements create the customer_t type and customer table under the person_t type and person table, respectively:

CREATE ROW TYPE customer_t
(
cust_num			INTEGER
) 
UNDER person_t;


CREATE TABLE customer OF TYPE customer_t
UNDER person;

Dropping a Table in a Table Hierarchy

If a table and its corresponding named row type have no dependencies (they are not a supertable and supertype), you can drop the table and its type. You must drop the table before you can drop the type. For information about how to drop a table, see the DROP TABLE statement in the Informix Guide to SQL: Syntax. For information about how to drop a named row type, see "Dropping Named Row Types".

Altering the Structure of a Table in a Table Hierarchy

You cannot use the ALTER TABLE statement to add or drop the columns of a table in a table hierarchy. Although you can use the ALTER TABLE statement to add or drop constraints, the following clauses in an ALTER TABLE statement are disallowed on typed tables:

ADD/DROP/MODIFY clauses
ADD ROWID/DROP ROWID clauses

Because of the preceding restrictions, the process of adding or dropping a column of a table in a table hierarchy (or otherwise altering the structure of a table) can be a time-intensive task.

To alter the structure of a table in a table hierarchy

1. Download data from all subtables and the supertable that you want to modify.

2. Drop the subtables and subtypes.

3. Modify the unloaded data file.

4. Modify the supertable.

5. Re-create the subtypes and subtables.

6. Upload the data.

Querying Tables in a Table Hierarchy

A table hierarchy allows you to construct a SELECT, UPDATE, or DELETE statement whose scope is a supertable and its subtables-in a single SQL command. For example, a query against any supertable in a table hierarchy returns data for all columns of the supertable and the columns that subtables inherit from the supertable. To limit the results of a query to one table in the table hierarchy, you must include the ONLY keyword in the query. For complete information about how to query and modify data from tables in a table hierarchy, see "Accessing Rows from Tables in a Table Hierarchy".

Creating a View on a Table in a Table Hierarchy

You can create a view based upon any table in a table hierarchy. For example, the following statement creates a view on the person table, which is the root supertable of the table hierarchy that Figure 10-6 shows:

CREATE VIEW name_view AS

SELECT name FROM person

Because the person table is a supertable, the view name_view displays data from the name column of the person, employee, and sales_rep tables. To create a view that displays only data from the person table, use the ONLY keyword, as the following example shows:

CREATE VIEW name_view AS

SELECT name FROM ONLY(person)

Important: You cannot perform an insert or update on a view that is defined on a supertable because the database server cannot know where in the table hierarchy to put the new rows.

For information about how to create a typed view, see "Creating Typed Views".