Top

9.8.6. UNDER

This allows the user to create a table as a sub-table of an existing table. A sub-table will inherit all columns and constraints of its super-table, most importantly the primary key. Primary keys shall be unique in the set of rows belonging to any direct or indirect sub-table of one super-table. A select from a super-table will see all rows, including those of any sub-tables, but not vice versa. Rows inserted directly into the super-table will not be visible to sub-tables.

The primary key option may not coexist with UNDER, since a subtable always inherits the supertable's primary key.

Example 9.21. Creating sub-tables using UNDER

Here a subtable will be created for the demo_table defined earlier. Both definitions are listed for convenience. Notice that the select over the sub-table (demo_sub_table) lists all columns whereas the select from super-table does not, however rows inserted into the super-table, demo_table, will not be seen in a select from the sub-table, but rows inserted into the sub-table will be shown in a select from the super-table.

CREATE TABLE demo_table (
  id INTEGER PRIMARY KEY,
  txtdata VARCHAR(20),
  )
;

CREATE TABLE demo_sub_table (
  new_col VARCHAR(10),
  UNDER demo_table
  )
;

SQL> select * from demo_table;
id                txtdata
INTEGER NOT NULL  VARCHAR
____________________________________________________________________

0 Rows. -- 20 msec.

SQL> select * from demo_sub_table;
id                txtdata               new_col
INTEGER NOT NULL  VARCHAR               VARCHAR
____________________________________________________________________

0 Rows. -- 10 msec.

Prev  Up  Next
9.8.5. PRIMARY KEY Constraint  Home  9.8.7. FOREIGN KEY Constraint