Sequences are always stored as 64-bit integers, regardless of the SQL dialect of the database.

    Caution

    If a client is connected using Dialect 1, the server handles sequence values as 32-bit integers. Passing a sequence value to a 32-bit field or variable will not cause errors as long as the current value of the sequence does not exceed the limits of a 32-bit number. However, as soon as the sequence value exceeds this limit, a database in Dialect 3 will produce an error. A database in Dialect 1 will truncate (overflow) the value, which could compromise the uniqueness of the series.

    This section describes how to create, alter, set and drop sequences.

    Used forCreating a new SEQUENCE (GENERATOR)

    Available inDSQL, ESQL

    Syntax

    Table 5.14.1.1 CREATE SEQUENCE Statement Parameters

    The statements CREATE SEQUENCE and CREATE GENERATOR are synonymous — both create a new sequence. Either can be used, but CREATE SEQUENCE is recommended as that is the syntax defined in the SQL standard.

    When a sequence is created, its current value is set so that the next value obtained from NEXT VALUE FOR *seq_name* is equal to start_value. In other words, the current value of the sequence is set to (*start_value* - *increment*). By default, the start_value is 1 (one).

    The optional INCREMENT [BY] clause allows you to specify an increment for the expression. By default, the increment is 1 (one). The increment cannot be set to 0 (zero). The GEN_ID(seq_name, <step>) function can be called instead, to step the series by a different integer number. The increment specified through INCREMENT [BY] is not used for GEN_ID.

    Non-standard behaviour for negative increments

    The SQL standard specifies that sequences with a negative increment should start at the maximum value of the sequence (263 - 1) and count down. Firebird does not do that, and instead starts at 1.

    This may change in a future Firebird version.

    5.14.1.1 Who Can Create a Sequence?

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

    • Users with the CREATE SEQUENCE (CREATE GENERATOR) privilege

    The user executing the CREATE SEQUENCE (CREATE GENERATOR) statement becomes its owner.

    5.14.1.2 Examples of CREATE SEQUENCE

    1. Creating the EMP_NO_GEN sequence using CREATE SEQUENCE.

      1. Creating the EMP_NO_GEN sequence using CREATE GENERATOR.

      2. Creating the EMP_NO_GEN sequence with an initial value of 5 and an increment of 1.

        1. CREATE SEQUENCE EMP_NO_GEN START WITH 5;

      3. Creating the EMP_NO_GEN sequence with an initial value of 1 and an increment of 10.

        1. CREATE SEQUENCE EMP_NO_GEN INCREMENT BY 10;

      4. Creating the EMP_NO_GEN sequence with an initial value of 5 and an increment of 10.

        1. CREATE SEQUENCE EMP_NO_GEN START WITH 5 INCREMENT BY 10;

      See also, Section 5.14.3, CREATE OR ALTER SEQUENCE, , Section 5.14.5, RECREATE SEQUENCE, , NEXT VALUE FOR,

      5.14.2 ALTER SEQUENCE

      Used forSetting the next value of a sequence or changing its increment

      Available inDSQL

      Syntax

      Table 5.14.2.1 ALTER SEQUENCE Statement Parameters

      ParameterDescription

      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 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, in 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.

      5.14.2.1 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

      5.14.2.2 Examples of ALTER SEQUENCE

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

        1. 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

        1. ALTER SEQUENCE EMP_NO_GEN RESTART;

      3. Changing the increment of sequence EMP_NO_GEN to 10

        1. ALTER SEQUENCE EMP_NO_GEN INCREMENT BY 10;

      See alsoSection 5.14.6, SET GENERATOR, , Section 5.14.3, CREATE OR ALTER SEQUENCE, , Section 5.14.5, RECREATE SEQUENCE, , GEN_ID() function

      Used forCreating a new or modifying an existing sequence

      Available inDSQL, ESQL

      Syntax

      1. CREATE OR ALTER {SEQUENCE | GENERATOR} seq_name
      2.   {RESTART | START WITH start_value}

      Table 5.14.3.1 CREATE OR ALTER SEQUENCE Statement Parameters

      If the sequence does not exist, it will be created. An existing sequence will be changed:

      • If RESTART is specified, the sequence will restarted with the initial value stored in the metadata

      • If the START WITH clause is specified, the sequence is restarted with start_value, but the start_value is not stored. In other words, it behaves as in .

      • If the INCREMENT [BY] clause is specified, increment is stored as the increment in the metadata, and used for subsequent calls to NEXT VALUE FOR

      5.14.3.1 Example of CREATE OR ALTER SEQUENCE

      Create a new or modify an existing sequence EMP_NO_GEN

      1. CREATE OR ALTER SEQUENCE EMP_NO_GEN
      2.   START WITH 10
      3.   INCREMENT BY 1

      See also, Section 5.14.2, ALTER SEQUENCE, , Section 5.14.5, RECREATE SEQUENCE, , NEXT VALUE FOR,

      5.14.4 DROP SEQUENCE

      Used forDropping (deleting) a SEQUENCE (GENERATOR)

      Available inDSQL, ESQL

      Syntax

      Table 5.14.4.1 DROP SEQUENCE Statement Parameter

      ParameterDescription

      seq_name

      Sequence (generator) name. The maximum length is 63 characters

      The statements DROP SEQUENCE and DROP GENERATOR statements are equivalent: both drop (delete) an existing sequence (generator). Either is valid but DROP SEQUENCE, being defined in the SQL standard, is recommended.

      5.14.4.1 Who Can Drop a Sequence?

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

      • Administrators

      • The owner of the sequence

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

      5.14.4.2 Example of DROP SEQUENCE

      Dropping the EMP_NO_GEN series:

      1. DROP SEQUENCE EMP_NO_GEN;

      See alsoSection 5.14.1, CREATE SEQUENCE, , Section 5.14.5, RECREATE SEQUENCE

      Used forCreating or recreating a sequence (generator)

      Available inDSQL, ESQL

      Syntax

      1. RECREATE {SEQUENCE | GENERATOR} seq_name
      2.   [START WITH start_value]
      3.   [INCREMENT [BY] increment]

      Table 5.14.5.1 RECREATE SEQUENCE Statement Parameters

      See for the full syntax of CREATE SEQUENCE and descriptions of defining a sequences and its options.

      RECREATE SEQUENCE creates or recreates a sequence. If a sequence with this name already exists, the RECREATE SEQUENCE statement will try to drop it and create a new one. Existing dependencies will prevent the statement from executing.

      5.14.5.1 Example of RECREATE SEQUENCE

      Recreating sequence EMP_NO_GEN

      1. RECREATE SEQUENCE EMP_NO_GEN
      2.   START WITH 10
      3.   INCREMENT BY 2;

      See also, Section 5.14.2, ALTER SEQUENCE, , Section 5.14.4, DROP SEQUENCE, , NEXT VALUE FOR,

      5.14.6 SET GENERATOR

      Used forSetting the current value of a sequence or generator to a specified value

      Available inDSQL, ESQL

      Syntax

      1. SET GENERATOR seq_name TO new_val

      Table 5.14.6.1 SET GENERATOR Statement Parameters

      ParameterDescription

      seq_name

      Generator (sequence) name

      new_val

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

      The SET GENERATOR statement sets the current value of a sequence or generator to the specified value.

      Note

      Although SET GENERATOR is considered outdated, it is retained for backward compatibility. Use of the standards-compliant ALTER SEQUENCE is recommended.

      5.14.6.1 Who Can Use a SET GENERATOR?

      The SET GENERATOR statement can be executed by:

      5.14.6.2 Example of SET GENERATOR

      Setting the value of the EMP_NO_GEN sequence to 145:

      Note

      Similar effects can be achieved with Section 5.14.2, ALTER SEQUENCE:

      Here, the value of increment is the current increment of the sequence. We need add it as ALTER SEQUENCE calculates the current value to set based on the next value it should produce.

      See also, Section 5.14.1, CREATE SEQUENCE, , Section 5.14.4, DROP SEQUENCE, , GEN_ID() function