From InterBase

Go Up to Statement and Function Reference (Language Reference Guide)

Sets a new value for an existing generator. Available in gpre, DSQL, and isql.

SET GENERATOR name TO <int>;
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.
Argument Description


Name of an existing generator


Value to which to set the generator, an integer from –263 to 263 – 1

Description: SET GENERATOR initializes a starting value for a newly created generator, or resets the value of an existing generator. A generator provides a unique, sequential numeric value through the GEN_ID() function. If a newly created generator is not initialized with SET GENERATOR, its starting value defaults to zero.

<int> is the new value for the generator. When the GEN_ID() function inserts or updates a value in a column, that value is <int> plus the increment specified in the GEN_ID()step parameter. Any value that can be stored in a DECIMAL(18,0) can be specified as the value in a SET GENERATOR statement.

Generators return a 64-bit value, and wrap around only after 264 invocations (assuming an increment of 1). Use an ISC-INT64 variable to hold the value returned by a generator.

To force a generator’s first insertion value to 1, use SET GENERATOR to specify a starting value of 0, and set the step value of the GEN_ID() function to 1.
When resetting a generator that supplies values to a column defined with ­PRIMARY KEY or UNIQUE integrity constraints, be careful that the new value does not enable duplication of existing column values, or all subsequent insertions and updates will fail.

Example: The following isql statement sets a generator value to 1,000:


If GEN_ID() now calls this generator with a step value of 1, the first number it returns is 1,001.

See Also

Advance To: