SEQUENCE (GENERATOR)

Table of Contents

CREATE SEQUENCE
ALTER SEQUENCE
SET GENERATOR
DROP SEQUENCE

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.

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.

CREATE SEQUENCE

Used for: Creating a new SEQUENCE (GENERATOR)

Available in: DSQL, ESQL

Syntax:

CREATE {SEQUENCE | GENERATOR} seq_name

Table 5.32. CREATE SEQUENCE | CREATE GENERATOR Statement Parameter

Parameter	Description
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:

Creating the EMP_NO_GEN series using CREATE SEQUENCE.

CREATE SEQUENCE EMP_NO_GEN;

Creating the EMP_NO_GEN series using CREATE GENERATOR.

CREATE GENERATOR EMP_NO_GEN;

ALTER SEQUENCE

Used for: Setting the value of a sequence or generator to a specified value

Available in: DSQL

Syntax:

ALTER SEQUENCE seq_name RESTART WITH new_val

Table 5.33. ALTER SEQUENCE Statement Parameters

Parameter	Description
seq_name	Sequence (generator) name
new_val	New sequence (generator) value. A 64-bit integer from -2^-63 to 2⁶³-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:

Setting the value of the EMP_NO_GEN sequence to 145.

ALTER SEQUENCE EMP_NO_GEN RESTART WITH 145;

Doing the same thing, using SET GENERATOR:

SET GENERATOR EMP_NO_GEN TO 145;

SET GENERATOR

Used for: Setting the value of a sequence or generator to a specified value

Available in: DSQL, ESQL

Syntax:

SET GENERATOR seq_name TO new_val

Table 5.34. SET GENERATOR Statement Parameters

Parameter	Description
seq_name	Generator (sequence) name
new_val	New sequence (generator) value. A 64-bit integer from -2^-63 to 2⁶³-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:

Setting the value of the EMP_NO_GEN sequence to 145:

SET GENERATOR EMP_NO_GEN TO 145;

Doing the same thing, using ALTER SEQUENCE:

ALTER SEQUENCE EMP_NO_GEN RESTART WITH 145;

DROP SEQUENCE

Used for: Deleting SEQUENCE (GENERATOR)

Available in: DSQL, ESQL

Syntax:

DROP {SEQUENCE | GENERATOR} seq_name

Table 5.35. DROP SEQUENCE | DROP GENERATOR Statement Parameter

Parameter	Description
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:

DROP SEQUENCE EMP_NO_GEN;