5.14. SEQUENCE (GENERATOR)

A sequence or a generator is a database object used to get unique number values to fill a series. Sequence is the SQL-compliant term for the same thing which, in Firebird, has traditionally been known as generator. Firebird has syntax for both terms.

Sequences (or generators) 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 sends sequence values to it 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 keep truncating the values, which will compromise the uniqueness of the series.

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

5.14.1. CREATE SEQUENCE

Used forCreating a new SEQUENCE (GENERATOR)

Available inDSQL, ESQL

Syntax 

CREATE {SEQUENCE | GENERATOR} seq_name
  [START WITH start_value]
  [INCREMENT [BY] increment]

Table 5.36. CREATE SEQUENCE Statement Parameters
ParameterDescription

seq_name

Sequence (generator) name. It may consist of up to 31 characters

start_value

Initial value of the sequence

increment

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

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 value is set to the value specified in the option START WITH clause. If there is no START WITH clause, then the sequence is set to 0.

The optional INCREMENT [BY] clause allows you to specify an increment for the NEXT VALUE FOR seq_name 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.

ⓘ
Bug with START WITH and INCREMENT [BY]

The SQL standard specifies that the START WITH clause should specify the initial value generated on the first call to NEXT VALUE FOR seq_name, but instead Firebird uses it to set the current value of the sequence. As a result the first call to NEXT VALUE FOR seq_name incorrectly generates the value start_value + increment.

Creating a sequence without a START WITH clause is currently equivalent to specifying START WITH 0, while it should be equivalent to START WITH 1.

This will be fixed in Firebird 4, see also CORE-6084

ⓘ
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 0 + increment.

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:

  • Administrators

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

    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. See note Bug with START WITH and INCREMENT [BY].

    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. See note Bug with START WITH and INCREMENT [BY].

    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. See note Bug with START WITH and INCREMENT [BY].

    CREATE SEQUENCE EMP_NO_GEN START WITH 5 INCREMENT BY 10;

See alsoSection 5.14.2, “ALTER SEQUENCE”, Section 5.14.3, “CREATE OR ALTER SEQUENCE”, Section 5.14.4, “DROP SEQUENCE”, Section 5.14.5, “RECREATE SEQUENCE”, Section 5.14.6, “SET GENERATOR”, NEXT VALUE FOR, GEN_ID() function

5.14.2. ALTER SEQUENCE

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

Available inDSQL

Syntax 

ALTER {SEQUENCE | GENERATOR} seq_name
  [RESTART [WITH newvalue]]
  [INCREMENT [BY] increment]

Table 5.37. 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 ALTER SEQUENCE statement sets the current value of a sequence or generator to the specified value and/or changes the increment of the sequence.

The RESTART WITH newvalue clause allows you to set the value of a sequence. The RESTART clause (without WITH) restarts the sequence with the initial value configured using the START WITH clause when the sequence was created.

ⓘ
Bugs with RESTART

The initial value (either saved in the metadata or specified in the WITH clause) is used to set the current value of the sequence, instead of the next value generated as required by the SQL standard. See note Bug with START WITH and INCREMENT [BY] for more information.

In addition, RESTART WITH newvalue will not only restart the sequence with the specified value, but also store newvalue as the new initial value of the sequence. This means that a subsequent ALTER SEQUENCE RESTART will also use newvalue. This behaviour does not match the behaviour specified in the SQL standard.

This bug will be fixed in Firebird 4, see also CORE-6386

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

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 to 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;

See alsoSection 5.14.6, “SET GENERATOR”, Section 5.14.1, “CREATE SEQUENCE”, Section 5.14.3, “CREATE OR ALTER SEQUENCE”, Section 5.14.4, “DROP SEQUENCE”, Section 5.14.5, “RECREATE SEQUENCE”, NEXT VALUE FOR, GEN_ID() function

5.14.3. CREATE OR ALTER SEQUENCE

Used forCreating a new or modifying an existing sequence

Available inDSQL, ESQL

Syntax 

CREATE OR ALTER {SEQUENCE | GENERATOR} seq_name
  {RESTART | START WITH start_value}
  [INCREMENT [BY] increment]

Table 5.38. CREATE OR ALTER SEQUENCE Statement Parameters
ParameterDescription

seq_name

Sequence (generator) name. It may consist of up to 31 characters

start_value

Initial value of the sequence

increment

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

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, start_value is stored as the initial value in the metadata, and the sequence is restarted

  • 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 

CREATE OR ALTER SEQUENCE EMP_NO_GEN
  START WITH 10
  INCREMENT BY 1

See alsoSection 5.14.1, “CREATE SEQUENCE”, Section 5.14.2, “ALTER SEQUENCE”, Section 5.14.4, “DROP SEQUENCE”, Section 5.14.5, “RECREATE SEQUENCE”, Section 5.14.6, “SET GENERATOR”, NEXT VALUE FOR, GEN_ID() function

5.14.4. DROP SEQUENCE

Used forDropping (deleting) a SEQUENCE (GENERATOR)

Available inDSQL, ESQL

Syntax 

DROP {SEQUENCE | GENERATOR} seq_name

Table 5.39. DROP SEQUENCE Statement Parameter
ParameterDescription

seq_name

Sequence (generator) name. It may consist of up to 31 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.

The statements will fail if the sequence (generator) has dependencies.

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: 

DROP SEQUENCE EMP_NO_GEN;

See alsoSection 5.14.1, “CREATE SEQUENCE”, Section 5.14.3, “CREATE OR ALTER SEQUENCE”, Section 5.14.5, “RECREATE SEQUENCE”

5.14.5. RECREATE SEQUENCE

Used forCreating or recreating a sequence (generator)

Available inDSQL, ESQL

Syntax 

RECREATE {SEQUENCE | GENERATOR} seq_name
  [START WITH start_value]
  [INCREMENT [BY] increment]

Table 5.40. RECREATE SEQUENCE Statement Parameters
ParameterDescription

seq_name

Sequence (generator) name. It may consist of up to 31 characters

start_value

Initial value of the sequence

increment

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

See Section 5.14.1, “CREATE SEQUENCE” 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 

RECREATE SEQUENCE EMP_NO_GEN
  START WITH 10
  INCREMENT BY 2;

See alsoSection 5.14.1, “CREATE SEQUENCE”, Section 5.14.2, “ALTER SEQUENCE”, Section 5.14.3, “CREATE OR ALTER SEQUENCE”, Section 5.14.4, “DROP SEQUENCE”, Section 5.14.6, “SET GENERATOR”, NEXT VALUE FOR, GEN_ID() function

5.14.6. SET GENERATOR

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

Available inDSQL, ESQL

Syntax 

SET GENERATOR seq_name TO new_val

Table 5.41. 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:

  • Administrators

  • The owner of the sequence (generator)

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

5.14.6.2. Example of SET GENERATOR

Setting the value of the EMP_NO_GEN sequence to 145: 

SET GENERATOR EMP_NO_GEN TO 145;

ⓘ
Note

The same can be done with Section 5.14.2, “ALTER SEQUENCE”:

ALTER SEQUENCE EMP_NO_GEN RESTART WITH 145;

See alsoSection 5.14.2, “ALTER SEQUENCE”, Section 5.14.1, “CREATE SEQUENCE”, Section 5.14.3, “CREATE OR ALTER SEQUENCE”, Section 5.14.4, “DROP SEQUENCE”, NEXT VALUE FOR, GEN_ID() function