Add 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. The following figure 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.

Two hierarchies of four items each are shown side-by-side. One is the type hierarchy and the other is the table hierarchy. In the hierarchies parents are on top of children. The type hierarchy is, from top to bottom: "person_t", "employee_t", "sales_rep_t", and "us_sales_rep_t". The inheritance arrow going from "sales_rep_t" to "us_sales_rep_t" is a dotted line. The rectangle around "us_sales_rep_t" is made of dotted lines. The table hierarchy is, from top to bottom: "person", "employee", "sales_rep", and "us_sales_rep". The inheritance arrow going from "sales_rep" to "us_sales_rep" is a dotted line. The rectangle around "us_sales_rep" is made of dotted lines. There are two directional arrows matching each item in the type hierarchy to exactly one item in the table hierarchy. "person_t" is matched with "person". "employee_t" is matched with "employee". "sales_rep_t" is matched to "sales_rep". "us_sales_rep_t" is matched to "us_sales_rep". — Figure 1: 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 Example of how you might add a subtype and subtable to an existing inheritance hierarchy 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. The following figure 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.

Two hierarchies of three items each are shown side-by-side. One is the type hierarchy and the other is the table hierarchy. In the hierarchies parents are on top of children. The type hierarchy is, from top to bottom: "person_t", "employee_t", and "sales_rep_t". "customer_t" is also present. It is in a dotted rectangle. There is also a dotted inheritance arrow going from "person_t" to "customer_t". A dotted inheritance arrow points down from "customer_t" to show that it might have other children that are not shown. The table hierarchy is, from top to bottom: "person", "employee", and "sales_rep". "customer" is also present. It is in a dotted rectangle. There is also a dotted inheritance arrow going from "person" to "customer". A dotted inheritance arrow points down from "customer" to show that it might have other children that are not shown. — Figure 2: 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;