FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 FILTEREXCEPTION 

A sequence — or 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 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 handles sequence values 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 truncate (overflow) the value, which could compromise the uniqueness of the series.

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

CREATE SEQUENCE

Creates a SEQUENCE (GENERATOR)

Available in

DSQL, ESQL

Syntax
CREATE {SEQUENCE | GENERATOR} seq_name
  [START WITH start_value]
  [INCREMENT [BY] increment]
Table 1. CREATE SEQUENCE Statement Parameters
Parameter Description

seq_name

Sequence (generator) name.The maximum length is 63 characters

start_value

Initial value of the sequence.Default is 1.

increment

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

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 current value is set so that the next value obtained from NEXT VALUE FOR seq_name is equal to start_value.In other words, the current value of the sequence is set to (start_value - increment).By default, the start_value is 1 (one).

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.

Note
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 1.

This may change in a future Firebird version.

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.

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.

    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.

    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.

    CREATE SEQUENCE EMP_NO_GEN START WITH 5 INCREMENT BY 10;
See also

[fblangref50-ddl-sequence-alter], [fblangref50-ddl-sequence-crtoralt], [fblangref50-ddl-sequence-drop], [fblangref50-ddl-sequence-recr], [fblangref50-ddl-sequence-setgen], NEXT VALUE FOR, GEN_ID() function