Adding a New Table to a Table Hierarchy

After 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 hierarchy provided that the new subtype and subtable do not interfere with existing inheritance relationships. Figure 34 illustrates one way that you might add a type and corresponding table to an existing hierarchy. The dashed lines indicate the added subtype and subtable.

Figure 34. 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 that Figure 34 shows:

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 35 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 35. 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;