Define 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 1 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 Modify 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 more information, see Under privileges for typed tables.