(redirected from Doc.Generator)
A generator is a database object and is part of the database's metadata. It is a sequential number, incorporating a whole-numbered 64 bit value
BIGINT (SQL dialect 3) since InterBase® 6/Firebird (in SQL dialect 1 it is a 32 bit value
INTEGER), that can automatically be inserted into a column. It is often used to ensure a unique value in an internal primary key.
A database can contain any number of generators and they can be used and updated in any transaction. They are the only transaction-independent part of Firebird/InterBase®. For each operation a new number is generated, regardless whether this transaction is ultimately committed or rolled back (this consequently leads to "missing numbers"). Therefore generators are best suited for automatic internal sequential numbering for internal primary keys.
SEQUENCE was introduced in Firebird 2.0. It is the SQL-99-compliant synonym for
SEQUENCE is a syntax term described in the SQL specification, whereas
GENERATOR is a legacy InterBase® syntax term.
It is recommended Firebird 2.0 users use the standard
A sequence generator is a mechanism for generating successive exact numeric values, one at a time. A sequence generator is a named schema object. In dialect 3 it is a
BIGINT, in dialect 1 it is an
INTEGER. It is often used to implement guaranteed unique IDs for records, to construct columns that behave like
AUTOINC fields found in other RDBMSs. Further information regarding
SEQUENCE can be found in the Firebird 2.0.4 Release Notes.
For legacy reasons, IBExpert will still continue to use the term
GENERATOR alongside the term
Generally a generator is used to determine unique identification numbers for primary keys. A
BEFORE INSERT TRIGGER can be defined for this, which increases the current value using the
GEN_ID() function, and automatically enters it in the respective table field. Please refer to Create a trigger for a generator for more information. A generator can also be called from a stored procedure or an application.
A database can contain any number of generators. Although up until the most recent InterBase® version 7.x the number of generators was limited to one data page. One generator uses 8 bytes, which means approximately 115 generators fit onto one page (at 1K). This limitation has been solved in the InterBase® 7.x version, and was solved in the Firebird 1.0 version. Using Firebird you can create more than 32,000 generators per database.
The current generator value of existing generators is not stored in a table but on its own system data pages, as the table contents are subject to transactional changes. The generator value is also secured when backing up.
A new generator can be created in a connected database in a number of ways:
- By using the menu item Database / New Generator, the respective icon in the New Database Object toolbar, or using the DB Explorer right mouse button (or key combination [Ctrl + N]), when the generator heading of the relevant connected database is highlighted, to start the New Generator Editor:
- Alternatively, a new generator can be created in the DB Explorer on the Fields page by double-clicking (or using the space bar when inserting a new field) to check the Autoinc box:
- Or in the Field Editor under Autoincrement (started by double-clicking on an existing
SMALLINTfield in the Table Editor).
- Or directly in the IBExpert SQL Editor, and then saved as a generator.
Using the the new generator name simply needs to be specified along with the initial generator value. Several generators can be created in the Generator Editor and compiled simultaneously:
Using the Display all Generators button on the Generator Editor toolbar, all generators for the database can be listed and an existing generator selected. (For internal numbering purposes, the same generator may be used on several fields, for example all internal primary key IDs, within the database.)
Using the Autoinc page in the Table and Field Editors, the Create Generator box simply needs to be checked, and the name and starting value defined.
It is also possible to select an existing generator for the specified field here (simply click Use Existing Generator and select from the drop-down list):
For those preferring direct SQL input, the syntax is as follows:
CREATE GENERATOR <Generator_Name>;
To include a description text when creating generators, add:
COMMENT ON SEQUENCE <Generator_Name> IS 'Description'
This statement also sets the initial generator value to zero. To establish a different starting value, use the
SET GENERATOR statement, for example:
SET GENERATOR <Generator_Name> TO n;
n is the initial generator value.
SET GENERATOR can also be used to reset an existing generator's value. This however requires care, as usually the column(s) that receives the generator value is/are defined to be unique. For example, you would not normally reset customer IDs except under unusual and controlled circumstances.
To increment the generator use the
STEP_VALUE parameter (can be positive or negative):
If this parameter is not used, the default
STEP_VALUE with an increment of 1 applies.
The Generator Editor can be started using the Database / New Generator menu item; from the DB Explorer, using the right mouse-click menu or double-clicking on an existing generator; or directly from the Field or Table Editor / Autoincrement.
Please refer to New Generator when creating a generator for the first time.
The Generator Editor has its own toolbar (see Generator Editor toolbar) and offers the following options:
In Firebird 2.0 the
RDB$DESCRIPTION field was added to
RDB$GENERATORS, so now it is now possible to include a description text when creating generators.
Please refer to Table Editor / Dependencies.
Please refer to Table Editor / DDL.
Creating - displays the
CREATE GENERATOR statement for the generator selected on the Generators page. If all generators are displayed on the Generator page (Display All Generators button), all corresponding
CREATE statements appear on this page.
Setting Values - displays the
SET GENERATOR statement for the generator selected on the Generators page. Again, if all generators are displayed on the Generator page (Display All Generators button), all
SET statements appear on this page.
Full - displays the full SQL text for the generator selected on the Generators page (or all generators).
Please note that the Scripts page is for display only. It is not possible to make any amendments on this page.
Please refer to Table Editor / Comparison.
Please refer to Table Editor / To-Do.
A generator may be altered to specify a new value. The value of a generator can be changed as often as wished.
This can be performed in IBExpert using the DB Explorer's Generator Editor, opened either by double-clicking on the generator name, or right-clicking and selecting Edit Generator [Ctrl + O]. Simply enter the new figure in the Value column, compile and commit.
The SQL syntax for altering a sequence is as follows:
SET SEQUENCE <sequence_name> RESTART WITH n
The SQL syntax for altering a generator is as follows:
SET GENERATOR <generator_name> TO n
n is the new value. This new value is immediately effective.
IBExpert asks for confirmation and displays the SQL statement:
before finally dropping when the statement is committed.
For those preferring to use SQL, the syntax is as follows:
DROP GENERATOR <generator_name>;
DROP GENERATOR command was introduced in Firebird 1, and does not exist in earlier InterBase® versions. If you need to delete a generator in an older InterBase® version, you will need to delete it from the system table,
DELETE FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME='GEN01';
Beware that this command deletes the specified generator regardless of any dependencies that may exist.
FB 2.0.4. Release Notes:
Firebird Internals: Generator page
Firebird for the database expert - Episode 2: Page Types
Create a trigger for a generator
Firebird Generator Guide