Inserting Values into Serial Columns

You can insert consecutive numbers, explicit values, or explicit values that reset the value in a SERIAL or SERIAL8 column:

• To insert a consecutive serial value

  Specify a zero (0) for the serial column in the INSERT statement. In this case, the database server assigns the next highest value.

• To insert an explicit value

  Specify the nonzero value after first verifying that it does not duplicate one already in the table. If the serial column is uniquely indexed or has a unique constraint, and your value duplicates one already in the table, an error results. If the value is greater than the current maximum value, you will create a gap in the series.

• To create a gap in the series (that is, to reset the serial value)

  Specify a positive value that is greater than the current maximum value in the column.

  Alternatively, you can use the MODIFY clause of the ALTER TABLE statement to reset the next value of a serial column.

For more information, see Altering the Next Serial Value.

NULL values are not valid in serial columns.

In Dynamic Server, inserting a serial value into a table that is part of a table hierarchy updates all tables in the hierarchy that contain the serial counter with the value that you insert. You can express this value either as zero (0) for the next highest value, or as a specific positive integer.