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 Example of the relationship between type hierarchy and table hierarchy 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.