SERIAL types in a table hierarchy

A table hierarchy can contain columns of type SERIAL and BIGSERIAL or SERIAL8. However, only one SERIAL and one BIGSERIAL or one SERIAL8 column are allowed across a table hierarchy. Suppose you create the following type and table hierarchy:

CREATE ROW TYPE parent_t (a INT);
CREATE ROW TYPE child1_t (s_col SERIAL) UNDER parent_t;
CREATE ROW TYPE child2_t (s8_col SERIAL8) UNDER child1_t;
CREATE ROW TYPE child3_t (d FLOAT) UNDER child2_t;

CREATE TABLE parent_tab of type parent_t; 
CREATE TABLE child1_tab of type child1_t UNDER parent_tab;
CREATE TABLE child2_tab of type child2_t UNDER child1_tab;
CREATE TABLE child3_tab of type child3_t UNDER child2_tab;

The parent_tab table does not contain a SERIAL type. The child1_tab introduces a SERIAL counter into the hierarchy. The child2_tab inherits the SERIAL column from child1_tab and adds a SERIAL8 column. The child3_tab inherits both a SERIAL and SERIAL8 column.

A 0 value inserted into the s_col or s8_col column for any table in the hierarchy inserts a monotonically increasing value, regardless of which table takes the insert.

You cannot set a starting counter value for a SERIAL or SERIAL8 type in CREATE ROW TYPE statements. To set a starting value for a SERIAL or SERIAL8 column in a table hierarchy, you can use the ALTER TABLE statement. The following statement shows how to alter a table to modify the next SERIAL and SERIAL8 values to be inserted anywhere in the table hierarchy:

ALTER TABLE child3_tab 
MODIFY (s_col SERIAL(100), s8_col SERIAL8 (200))

Except for the previously described behavior, all the rules that apply to SERIAL, BIGSERIAL, and SERIAL8 type columns in untyped tables also apply to SERIAL, BIGSERIAL, and SERIAL8 type columns in table hierarchies. For more information, see Select data types and the HCL OneDB™ Guide to SQL: Reference.