Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
ALTER TABLE statementUse the ALTER TABLE statement to modify the schema of an existing table.
MODIFY Clause Use the MODIFY clause to change the data type, length, or default value of a column, to add or remove the security label of a column, to allow or disallow NULL values in a column, or to reset the serial counter of a SERIAL, SERIAL8, or BIGSERIAL column.
Altering the Next Serial Value

Altering the Next Serial Value

You can use the MODIFY clause to reset the next value of a SERIAL, BIGSERIAL, 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.