5.11. 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. Both terms are implemented in Firebird, which recognises and 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 cutting the values, which will compromise the uniqueness of the series.

This section describes how to create, set and delete sequences.

5.11.1. CREATE SEQUENCE (GENERATOR)

Used forCreating a new SEQUENCE (GENERATOR)

Available inDSQL, ESQL

Syntax 

  |CREATE {SEQUENCE | GENERATOR} seq_name

Table 5.32. CREATE SEQUENCE | CREATE GENERATOR Statement Parameter
ParameterDescription

seq_name

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

The statements CREATE SEQUENCE and CREATE GENERATOR are synonymous — both create a new sequence. Either can be used but CREATE SEQUENCE is recommended if standards-compliant metadata management is important.

When a sequence is created, its value is set to 0. Each time the NEXT VALUE FOR seq_name operator is used with that sequence, its value increases by 1. The GEN_ID(seq_name, <step>) function can be called instead, to step the series by a different integer number.

Any user connected to the database can create a sequence (generator).

Examples

  1. Creating the EMP_NO_GEN series using CREATE SEQUENCE.

      |CREATE SEQUENCE EMP_NO_GEN;

  2. Creating the EMP_NO_GEN series using CREATE GENERATOR.

      |CREATE GENERATOR EMP_NO_GEN;

See alsoSection 5.11.2, “ALTER SEQUENCE”, Section 5.11.3, “SET GENERATOR”, Section 5.11.4, “DROP SEQUENCE (GENERATOR)”, NEXT VALUE FOR, GEN_ID() function

5.11.2. ALTER SEQUENCE

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

Available inDSQL

Syntax 

  |ALTER SEQUENCE seq_name RESTART WITH new_val

Table 5.33. ALTER SEQUENCE Statement Parameters
ParameterDescription

seq_name

Sequence (generator) name

new_val

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

The ALTER SEQUENCE statement sets the current value of a sequence or generator to the specified value.

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

Any user connected to the database can set the sequence (generator) value.

Examples

  1. Setting the value of the EMP_NO_GEN sequence to 145.

      |ALTER SEQUENCE EMP_NO_GEN RESTART WITH 145;

  2. Doing the same thing, using SET GENERATOR:

      |SET GENERATOR EMP_NO_GEN TO 145;

See alsoSection 5.11.3, “SET GENERATOR”, Section 5.11.1, “CREATE SEQUENCE (GENERATOR)”, Section 5.11.4, “DROP SEQUENCE (GENERATOR)”, NEXT VALUE FOR, GEN_ID() function

5.11.3. 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.34. 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. Using the standards-compliant ALTER SEQUENCE is current and is recommended.

Any user connected to the database can set the sequence (generator) value.

Examples

  1. Setting the value of the EMP_NO_GEN sequence to 145:

      |SET GENERATOR EMP_NO_GEN TO 145;

  2. Doing the same thing, using ALTER SEQUENCE:

      |ALTER SEQUENCE EMP_NO_GEN RESTART WITH 145;

See alsoSection 5.11.2, “ALTER SEQUENCE”, Section 5.11.1, “CREATE SEQUENCE (GENERATOR)”

5.11.4. DROP SEQUENCE (GENERATOR)

Used forDeleting SEQUENCE (GENERATOR)

Available inDSQL, ESQL

Syntax 

  |DROP {SEQUENCE | GENERATOR} seq_name

Table 5.35. DROP SEQUENCE | DROP GENERATOR 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 delete an existing sequence (generator). Either is valid but DROP SEQUENCE, being current, is recommended.

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

Any user connected to the database can drop a sequence (generator).

ExampleDropping the EMP_NO_GEN series:

  |DROP SEQUENCE EMP_NO_GEN;

See alsoSection 5.11.1, “CREATE SEQUENCE (GENERATOR)”, Section 5.11.2, “ALTER SEQUENCE”, Section 5.11.3, “SET GENERATOR”