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.