SEQUENCE or GENERATOR

Table of Contents

CREATE SEQUENCE
CREATE GENERATOR
ALTER SEQUENCE
SET GENERATOR
DROP SEQUENCE
DROP GENERATOR

Tip

Find a more recent version at Firebird 5.0 Language Reference: SEQUENCE (GENERATOR)

CREATE SEQUENCE

Available in: DSQL

Added in: 2.0

Description: Creates a new sequence or generator. SEQUENCE is the SQL-compliant term for what InterBase and Firebird have always called a generator. CREATE SEQUENCE is fully equivalent to CREATE GENERATOR and is the recommended syntax from Firebird 2.0 onward.

Syntax:

CREATE SEQUENCE sequence-name

Example:

create sequence seqtest

Because internally sequences and generators are the same thing, you can freely mix the generator and sequence syntaxes, even when operating on the same object. This is not recommended however.

Sequences (or generators) are always stored as 64-bit integer values, regardless of the database dialect. However:

If the client dialect is set to 1, the server passes generator values as truncated 32-bit values to the client.
If generator values are fed into a 32-bit field or variable, all goes well until the actual value exceeds the 32-bit range. At that point, a dialect 3 database will raise an error whereas a dialect 1 database will silently truncate the value (which could also lead to an error, e.g. if the receiving field has a unique key defined on it).

CREATE GENERATOR

Table of Contents

CREATE SEQUENCE preferred
Maximum number of generators significantly raised

Available in: DSQL, ESQL

Better alternative: CREATE SEQUENCE

CREATE SEQUENCE preferred

Changed in: 2.0

Description: From Firebird 2.0 onward, the SQL-compliant CREATE SEQUENCE syntax is preferred.

Maximum number of generators significantly raised

Changed in: 1.0

Description: InterBase reserved only one database page for generators, limiting the total number to 123 (on 1K pages) – 1019 (on 8K pages). Firebird has done away with that limit; you can now create more than 32,000 generators per database.

ALTER SEQUENCE

Available in: DSQL

Added in: 2.0

Description: (Re)initializes a sequence or generator to the given value. SEQUENCE is the SQL-compliant term for what InterBase and Firebird have always called a generator. “ALTER SEQUENCE ... RESTART WITH” is fully equivalent to “SET GENERATOR ... TO” and is the recommended syntax from Firebird 2.0 onward.

Syntax:

ALTER SEQUENCE sequence-name RESTART WITH <newval>

<newval>  ::=  A signed 64-bit integer value.

Example:

alter sequence seqtest restart with 0

Warning

Careless use of ALTER SEQUENCE is a mighty fine way of screwing up your database! Under normal circumstances you should only use it right after CREATE SEQUENCE, to set the initial value.

SET GENERATOR

Available in: DSQL, ESQL

Better alternative: ALTER SEQUENCE

Description: (Re)initializes a generator or sequence to the given value. From Firebird 2 onward, the SQL-compliant ALTER SEQUENCE syntax is preferred.

Syntax:

SET GENERATOR generator-name TO <new-value>

<new-value>  ::=  A 64-bit integer.

Warning

Once a generator or sequence is up and running, you should not tamper with its value (other than retrieving next values with GEN_ID or NEXT VALUE FOR) unless you know exactly what you are doing.

DROP SEQUENCE

Available in: DSQL

Added in: 2.0

Description: Removes a sequence or generator from the database. Its (very small) storage space will be freed for re-use after a backup-restore cycle. SEQUENCE is the SQL-compliant term for what InterBase and Firebird have always called a generator. DROP SEQUENCE is fully equivalent to DROP GENERATOR and is the recommended syntax from Firebird 2.0 onward.

Syntax:

DROP SEQUENCE sequence-name

Example:

drop sequence seqtest

DROP GENERATOR

Available in: DSQL

Added in: 1.0

Better alternative: DROP SEQUENCE

Description: Removes a generator or sequence from the database. Its (very small) storage space will be freed for re-use after a backup-restore cycle.

Syntax:

DROP GENERATOR generator-name

From Firebird 2.0 onward, the SQL-compliant DROP SEQUENCE syntax is preferred.