FirebirdSQL logo
 FILTEREXCEPTION 

Examples of ALTER SEQUENCE

  1. Setting the value of the EMP_NO_GEN sequence so the next value is 145.

    ALTER SEQUENCE EMP_NO_GEN RESTART WITH 145;

  2. Resetting the base value of the sequence EMP_NO_GEN to the initial value stored in the metadata

    ALTER SEQUENCE EMP_NO_GEN RESTART;

  3. Changing the increment of sequence EMP_NO_GEN to 10

    ALTER SEQUENCE EMP_NO_GEN INCREMENT BY 10;
See also

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

CREATE OR ALTER SEQUENCE

Creates a sequence if it doesn’t exist, or alters a sequence

Available in

DSQL, ESQL

Syntax
CREATE OR ALTER {SEQUENCE | GENERATOR} seq_name
  {RESTART | START WITH start_value}
  [INCREMENT [BY] increment]
Table 1. CREATE OR ALTER 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.

If the sequence does not exist, it will be created.An existing sequence will be changed:

  • If RESTART is specified, the sequence will restart with the initial value stored in the metadata

  • If the START WITH clause is specified, the sequence is restarted with start_value, but the start_value is not stored.In other words, it behaves as RESTART WITH in [fblangref50-ddl-sequence-alter].

  • If the INCREMENT [BY] clause is specified, increment is stored as the increment in the metadata, and used for subsequent calls to NEXT VALUE FOR

Example of CREATE OR ALTER SEQUENCE

Create a new or modify an existing sequence EMP_NO_GEN
CREATE OR ALTER SEQUENCE EMP_NO_GEN
  START WITH 10
  INCREMENT BY 1
See also

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

DROP SEQUENCE

Drops a SEQUENCE (GENERATOR)

Available in

DSQL, ESQL

Syntax
DROP {SEQUENCE | GENERATOR} seq_name
Table 1. DROP SEQUENCE Statement Parameter
Parameter Description

seq_name

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

The statements DROP SEQUENCE and DROP GENERATOR statements are equivalent: both drop (delete) an existing sequence (generator).Either is valid but DROP SEQUENCE, being defined in the SQL standard, is recommended.

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

Who Can Drop a Sequence?

The DROP SEQUENCE (DROP GENERATOR) statement can be executed by:

  • Administrators

  • The owner of the sequence

  • Users with the DROP ANY SEQUENCE (DROP ANY GENERATOR) privilege

Example of DROP SEQUENCE

Dropping the EMP_NO_GEN series:
DROP SEQUENCE EMP_NO_GEN;
See also

[fblangref50-ddl-sequence-create], [fblangref50-ddl-sequence-crtoralt], [fblangref50-ddl-sequence-recr]

RECREATE SEQUENCE

Drops a sequence if it exists, and creates a sequence (generator)

Available in

DSQL, ESQL

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

seq_name

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

start_value

Initial value of the sequence

increment

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

See [fblangref50-ddl-sequence-create] for the full syntax of CREATE SEQUENCE and descriptions of defining a sequences and its options.

RECREATE SEQUENCE creates or recreates a sequence.If a sequence with this name already exists, the RECREATE SEQUENCE statement will try to drop it and create a new one.Existing dependencies will prevent the statement from executing.

Example of RECREATE SEQUENCE

Recreating sequence EMP_NO_GEN
RECREATE SEQUENCE EMP_NO_GEN
  START WITH 10
  INCREMENT BY 2;
See also

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

SET GENERATOR

Sets the current value of a sequence (generator)

Available in

DSQL, ESQL

Syntax
SET GENERATOR seq_name TO new_val
Table 1. 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 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.Use of the standards-compliant ALTER SEQUENCE is recommended.

Who Can Use a SET GENERATOR?

The SET GENERATOR statement can be executed by:

  • Administrators

  • The owner of the sequence (generator)

  • Users with the ALTER ANY SEQUENCE (ALTER ANY GENERATOR) privilege

Example of SET GENERATOR

Setting the value of the EMP_NO_GEN sequence to 145:
SET GENERATOR EMP_NO_GEN TO 145;
Note

Similar effects can be achieved with [fblangref50-ddl-sequence-alter]:

ALTER SEQUENCE EMP_NO_GEN
  RESTART WITH 145 + increment;

Here, the value of increment is the current increment of the sequence.We need add it as ALTER SEQUENCE calculates the current value to set based on the next value it should produce.
See also

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