# Integer Types - Firebird

All integer types are signed exact numerics having a scale of zero. Firebird supports three named ranges of precision as integer data types: SMALLINT, INTEGER, and BIGINT.

• SMALLINT is a signed short integer with a range from –32,768 to 32,767.
• INTEGER is a signed long integer with a range from –2,147,483,648 to 2,147,483,647.
• BIGINT is a signed 64-bit integer with a range from 2-63 to 263 – 1. Not available in dialect 1.

For more information about scale, precision, and the operations that can be performed on fixed types, refer to the upcoming section “Fixed-Decimal (Scaled) Types.”

The next two statements create a domain and a column with the SMALLINT and INTEGER data types respectively:

CREATE DOMAIN RGB_RED_VALUE AS SMALLINT; /**/ CREATE TABLE STUDENT_ROLL ( STUDENT_ID INTEGER, ...);

Each of these statements creates a domain that is a 64-bit integer:

CREATE DOMAIN IDENTITY BIGINT CHECK (VALUE >=0); /* Firebird 1.5 and higher */ CREATE DOMAIN IDENTITY NUMERIC(18,0) CHECK (VALUE >=0);

SMALLINT

SMALLINT is a 2-byte integer providing compact storage for whole numbers with a limited range. For example, SMALLINT would be suitable for storing the value of colors in the RGB scale, as in the previous domain example.

SMALLINT is often used to define a two-state Boolean, usually 0=False, 1=True. An example of this usage can be found in the section “Defining a BOOLEAN Domain”

INTEGER

INTEGER is a 4-byte integer. In dialect 1, generators (see the “Generators” section) generate integers. You can store integers in BIGINT columns without casting.

BIGINT, NUMERIC(18,0)

Available in dialect 3 only, this is an 8-byte integer, useful for storing whole numbers with very low and high ranges. In dialect 3, generators (see the “Generators” section) generate BIGINT numbers.

Autoincrement or Identity Type

Firebird has no autoincrement or identity type such as you find in some other database management systems. What it does have is a number-generator engine and the ability to maintain independent, named series of BIGINT numbers. Each series is known as a generator. A technique for using them to implement and maintain primary keys and other automatically incrementing series is described in Chapter Triggers.

Generators

Generators are ideal for using to populate an automatically incrementing unique key or a stepping serial number column or other series. Generators are declared in a database using a CREATE statement, just like any other database object:

CREATE GENERATOR AGenerator;

Generators can be set to any starting value:

SET GENERATOR AGenerator TO 1;

Calling for the Next Value

To call for the next value, invoke the SQL function GEN_ID(GeneratorName, n), where GeneratorName is the name of the generator and n is an integer (dialect 1) or NUMERIC(18,0) (dialect 3) specifying the size of the step. The query SELECT GEN_ID(AGenerator, 2) from RDB$DATABASE; returns a number that is 2 greater than the last generated number and increments the current value of the generator to the value it just generated. Current Value of a Generator This line: SELECT GEN_ID(AGenerator, 0) from RDB$DATABASE;

returns the current value of the generator, without incrementing it. PSQL, Firebird’s programming language, allows a value to be generated directly into a variable:

... DECLARE VARIABLE MyVar BIGINT; ... MyVar = GEN_ID(AGenerator, 1);

Using Negative Stepping

The step argument of GEN_ID(..) can be negative. Thus, it is possible to set or reset a generator’s current value by passing a negative argument as either an integer constant or an integer expression. This capability is sometimes used as a “trick” for meddling with generator values in PSQL, since PSQL does not allow DDL commands such as SET GENERATOR.

For example, the statement

SELECT GEN_ID(AGenerator, ((SELECT GEN_ID(AGenerator, 0) from RDB$DATABASE) * -1) from RDB$DATABASE;

causes the generator to be reset to zero.

The general rule of thumb about resetting generator values in production databases— whether through SQL, PSQL, or some admin interface—is don’t.

The benefit of generator values is that they are guaranteed to be unique. Unlike any other user-accessible operation in Firebird, generators operate outside transaction control. Once generated, a number is “gone” and cannot be reverted by transaction rollback. This absolutely assures the integrity of number sequences, provided the generators are not tampered with.

Reserve the resetting of generators in a production database for the rare circumstances where a design requirement calls for it. For example, some older-style accounting systems pass journals into history tables with new primary keys, empty the journal table, and reset the primary key sequence to zero or, in multi-site organizations, separated ranges of key values are allocated to each site in “chunks” to ensure key integrity on replication.

Never reset generators in an attempt to correct bugs or input errors or to “fill gaps” in a sequence.