FirebirdSQL logo
 FILTEREXCEPTION 

Examples of CREATE SEQUENCE

  1. Creating the EMP_NO_GEN sequence using CREATE SEQUENCE.

    CREATE SEQUENCE EMP_NO_GEN;
  2. Creating the EMP_NO_GEN sequence using CREATE GENERATOR.

    CREATE GENERATOR EMP_NO_GEN;
  3. Creating the EMP_NO_GEN sequence with an initial value of 5 and an increment of 1.

    CREATE SEQUENCE EMP_NO_GEN START WITH 5;
  4. Creating the EMP_NO_GEN sequence with an initial value of 1 and an increment of 10.

    CREATE SEQUENCE EMP_NO_GEN INCREMENT BY 10;
  5. Creating the EMP_NO_GEN sequence with an initial value of 5 and an increment of 10.

    CREATE SEQUENCE EMP_NO_GEN START WITH 5 INCREMENT BY 10;

ALTER SEQUENCE

Sets the next value of a sequence, or changes its increment

Available in

DSQL

Syntax
ALTER {SEQUENCE | GENERATOR} seq_name
  [RESTART [WITH newvalue]]
  [INCREMENT [BY] increment]
Table 1. ALTER SEQUENCE Statement Parameters
Parameter Description

seq_name

Sequence (generator) name

newvalue

New sequence (generator) value.A 64-bit integer from -2-63 to 263-1.

increment

Increment of the sequence (when using NEXT VALUE FOR seq_name);cannot be 0.

The ALTER SEQUENCE statement sets the current value of a sequence to the specified valueand/or changes the increment of the sequence.

The RESTART WITH newvalue clause allows you to set the next value generated by NEXT VALUE FOR seq_name.To achieve this, the current value of the sequence is set to (newvalue - increment) with increment either as specified in the statement, or stored in the metadata of the sequence.The RESTART clause (without WITH) restarts the sequence with the initial value stored in the metadata of the sequence.

Note

Contrary to Firebird 3.0, since Firebird 4.0 RESTART WITH newvalue only restarts the sequence with the specified value, and does not store newvalue as the new initial value of the sequence.A subsequent ALTER SEQUENCE RESTART will use the initial value specified when the sequence was created, and not the newvalue of this statement.This behaviour is specified in the SQL standard.

It is currently not possible to change the initial value stored in the metadata.

Warning

Incorrect use of the ALTER SEQUENCE statement (changing the current value of the sequence or generator) is likely to break the logical integrity of data, or result in primary key or unique constraint violations.

INCREMENT [BY] allows you to change the sequence increment for the NEXT VALUE FOR expression.

Note

Changing the increment value takes effect for all queries that run after the transaction commits.Procedures that are called for the first time after changing the commit, will use the new value if they use NEXT VALUE FOR.Procedures that were already used (and cached in the metadata cache) will continue to use the old increment.You may need to close all connections to the database for the metadata cache to clear, and the new increment to be used.Procedures using NEXT VALUE FOR do not need to be recompiled to see the new increment.Procedures using GEN_ID(gen, expression) are not affected when the increment is changed.

Who Can Alter a Sequence?

The ALTER SEQUENCE (ALTER GENERATOR) statement can be executed by:

  • Administrators

  • The owner of the sequence

  • Users with the ALTER ANY SEQUENCE (ALTER ANY GENERATOR) privilege

Examples of ALTER SEQUENCE

  1. Setting the value of the EMP_NO_GEN sequence so the next value is 145.

    ALTER SEQUENCE EMP_NO_GEN RESTART WITH 145;
  2. Resetting the base value of the sequence EMP_NO_GEN to the initial value stored in the metadata

    ALTER SEQUENCE EMP_NO_GEN RESTART;
  3. Changing the increment of sequence EMP_NO_GEN to 10

    ALTER SEQUENCE EMP_NO_GEN INCREMENT BY 10;