Using the UNDER Clause

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > CREATE TABLE >

Using the UNDER Clause

Use the UNDER clause to specify inheritance (that is, define the new table as a subtable). The subtable inherits properties from the specified supertable. In addition, you can define new properties specific to the subtable.

Continuing the example shown in OF TYPE Clause (IDS), the following statements create a typed table, grad_students, that inherits all of the columns of the students table but also has columns for adviser and field_of_study that correspond to fields in the grad_student_t ROW type:

CREATE ROW TYPE grad_student_t 
   (adviser        CHAR(25),
    field_of_study CHAR(40)) UNDER student_t;

CREATE TABLE grad_students OF TYPE grad_student_t  UNDER students;

When you use the UNDER clause, the subtable inherits these properties:

All columns in the supertable
All constraints defined on the supertable
All indexes defined on the supertable
All triggers defined on the supertable.
All referential integrity constraints
The access method
The storage option specification (including fragmentation strategy)
If a subtable defines no fragments, but if its supertable has fragments defined, then the subtable inherits the fragments of the supertable.

Tip:

Any heritable attributes that are added to a supertable after subtables have been created are automatically inherited by existing subtables. You do not need to add all heritable attributes to a supertable before you create its subtables.

Restrictions on Table Hierarchies

Inheritance occurs in one direction only, namely from supertable to subtable. Properties of subtables are not inherited by supertables. The section System Catalog Information lists the inherited database objects for which the system catalog maintains no information regarding subtables.

No two tables in a table hierarchy can have the same data type. For example, the final line of the next code example is invalid, because the tables tab2 and tab3 cannot have the same row type (rowtype2):

         create row type rowtype1 (...);
         create row type rowtype2 (...) under rowtype1;
         create table tab1 of type rowtype1;
         create table tab2 of type rowtype2 under tab1;
--Invalid -->            create table tab3 of type rowtype2 under tab1;