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.

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 for

Creating a new SEQUENCE (GENERATOR)

Available in

DSQL, ESQL

Syntax

  1. CREATE {SEQUENCE | GENERATOR} seq_name
Table 47. 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.

    1. CREATE SEQUENCE EMP_NO_GEN;

  2. Creating the EMP_NO_GEN series using CREATE GENERATOR.

    1. CREATE GENERATOR EMP_NO_GEN;

See also

ALTER SEQUENCE, SET GENERATOR, DROP SEQUENCE (GENERATOR), NEXT VALUE FOR, GEN_ID() function

5.11.2. ALTER SEQUENCE

Used for

Setting the value of a sequence or generator to a specified value

Available in

DSQL

Syntax

  1. ALTER SEQUENCE seq_name RESTART WITH new_val
Table 48. 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.

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.

    1. ALTER SEQUENCE EMP_NO_GEN RESTART WITH 145;

  2. Doing the same thing, using SET GENERATOR:

    1. SET GENERATOR EMP_NO_GEN TO 145;

See also

SET GENERATOR, CREATE SEQUENCE (GENERATOR), DROP SEQUENCE (GENERATOR), NEXT VALUE FOR, GEN_ID() function

5.11.3. SET GENERATOR

Used for

Setting the value of a sequence or generator to a specified value

Available in

DSQL, ESQL

Syntax

  1. SET GENERATOR seq_name TO new_val
Table 49. 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.

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:

    1. SET GENERATOR EMP_NO_GEN TO 145;

  2. Doing the same thing, using ALTER SEQUENCE:

    1. ALTER SEQUENCE EMP_NO_GEN RESTART WITH 145;

See also

ALTER SEQUENCE, CREATE SEQUENCE (GENERATOR)

5.11.4. DROP SEQUENCE (GENERATOR)

Used for

Deleting SEQUENCE (GENERATOR)

Available in

DSQL, ESQL

Syntax

  1. DROP {SEQUENCE | GENERATOR} seq_name
Table 50. 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).

Example

Dropping the EMP_NO_GEN series:

  1. DROP SEQUENCE EMP_NO_GEN;

See also

CREATE SEQUENCE (GENERATOR), ALTER SEQUENCE, SET GENERATOR