Altering the Next Serial Value

You can use the MODIFY clause to reset the next value of a SERIAL or SERIAL8 column. You cannot set the next value below the current maximum value in the column because that action can cause the database server to generate duplicate numbers. You can set the next value, however, to any value higher than the current maximum, which creates a gap in the series of values.

If the new serial value that you specify is less than the current maximum value in the serial column, the maximum value is not altered. If the maximum value is less than what you specify, the next serial number will be what you specify. The next serial value is not equivalent to one greater than the maximum serial value in the column in two situations:

• There are no rows in the table, and an initial serial value was specified when the table was created (or by a previous ALTER TABLE statement).
• There are rows in the table, but the next serial value was modified by a previous ALTER TABLE statement.

The following example sets the next serial value to 1000:

ALTER TABLE my_table MODIFY (serial_num serial (1000))

As an alternative, you can use the INSERT statement to create a gap in the series of serial values in the column. For more information, see Inserting Values into Serial Columns.

Altering the Next Serial Value in a Typed Table (IDS)

You can set the initial serial number or modify the next serial number for a ROW-type field with the MODIFY clause of the ALTER TABLE statement. (You cannot set the initial number for a serial field when you create a ROW data type.)

Suppose you have ROW types parent, child1, child2, and child3.

CREATE ROW TYPE parent (a int);
CREATE ROW TYPE child1 (s serial) UNDER parent;
CREATE ROW TYPE child2 (b float, s8 serial8) UNDER child1;
CREATE ROW TYPE child3 (d int) UNDER child2;

You then create corresponding typed tables:

CREATE TABLE OF TYPE parent;
CREATE TABLE OF TYPE child1 UNDER parent;
CREATE TABLE OF TYPE child2 UNDER child1;
CREATE TABLE OF TYPE child3 UNDER child2;

To change the next SERIAL and SERIAL8 numbers to 75, you can issue the following statement:

ALTER TABLE child3 MODIFY (s serial(75), s8 serial8(75))

When the ALTER TABLE statement executes, the database server updates corresponding serial columns in the child1, child2, and child3 tables.