The Firebird 5.0 Language Reference | CREATE OR ALTER SEQUENCE

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

`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;

`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;

`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 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.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.

Example Of CREATE OR ALTER SEQUENCE

Example of CREATE OR ALTER SEQUENCE

DROP SEQUENCE

Who Can Drop a Sequence?

Example of DROP SEQUENCE

RECREATE SEQUENCE

Example of RECREATE SEQUENCE

SET GENERATOR

Who Can Use a SET GENERATOR?

Example of SET GENERATOR

Example of `CREATE OR ALTER SEQUENCE`

`DROP SEQUENCE`

Example of `DROP SEQUENCE`

`RECREATE SEQUENCE`

Example of `RECREATE SEQUENCE`

`SET GENERATOR`

Who Can Use a `SET GENERATOR`?

Example of `SET GENERATOR`