ALTER SEQUENCE Statement

statement_ALTER_SEQUENCE :== ALTER sequence_description

The ALTER SEQUENCE statement modifies the existing definition of a sequence or changes the next value of a sequence. The old definition is replaced with the new one according to the description.

Parameters that are not set in the ALTER SEQUENCE statement are preserved from the original definition when modifying a sequence.

A sequence after modification continues in the numerical series as it was before modification, except for two cases. If the clause RESTART WITH new_value is set in the statement along with the modified parameters, then the new_value is set as the next value of the sequence. If the statement is called without parameters, then the initial value is set as the next value (sequence reset).

The clause AS for type specification cannot be used in the ALTER SEQUENCE statement.

Execution of this statement always ends an open transaction.

Alternatively, you can modify a sequence from the Control Panel of the 602SQL Client.

Example:

When initializing, change the next value of a sequence to the value which is the maximum key value in a table +1:

  DECLARE maxid INT;
  SELECT MAX(id)+1 INTO maxid FROM Tab;

  CALL SQL_execute('ALTER SEQUENCE seqx RESTART WITH '||Int2str(maxid));

602SQL Variations

The START WITH clause can be used in the ALTER SEQUENCE statement as an extension to the SQL 2003 standard. This statement changes the starting value of the sequence and if the RESTART WITH clause is not specified, sets the actual sequence value to the new one.

See

CREATE SEQUENCE Statement

Sequences

DROP SEQUENCE Statement