Easiest way to create an auto increment field in Firebird database

by

Firebird 3 and later

In Firebird 3 it is easy to create, as it introduced identity columns. In practice it is syntactic sugar for generating a sequence + trigger (as shown for Firebird 2.5) for you.

For example

create table t1 (
   id integer generated by default as identity primary key
)

Firebird 3 only supports “generated by default“, which means users are able to specify their own id values (which might lead to duplicate value errors); “generated always” has been added in Firebird 4.

See also the Firebird 3 release notes, section “Identity Column Type“, and the Firebird 4.0 Language Reference, section “Identity Columns (Autoincrement)“.

Firebird 2.5 and earlier

Firebird 2.5 and earlier do not have auto-increment fields. You need to create them yourself with a sequence (aka generator) and a trigger.

Sequence is the SQL standard term and generator is the historical Firebird term; both terms are available in the Firebird DDL syntax.

To create a sequence:

CREATE SEQUENCE t1_id_sequence;

To create a trigger to always generate the id on a table T1 with primary key ID:

set term !! ;
CREATE TRIGGER T1_AUTOINCREMENT FOR T1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW.ID = next value for t1_id_sequence;
END!!
set term ; !!

See also: How to create an autoincrement column?

Using FlameRobin

FlameRobin also provides tooling to create a sequence + trigger for you. If you have an existing table, you can follow these steps:

  1. Open the table properties:

    open table properties

  2. Open the column properties of the primary key column

    open column properties

  3. Default column properties, select new generator and create trigger:

    default column properties

  4. Generator (sequence) and trigger code generated by FlameRobin. Note that contrary to my example above this trigger allows a user to specify their own id value, with some logic to avoid future duplicates. Execute this (and don’t forget to commit):

    generated code for generator + trigger

More Related Contents:

  1. Why does WPF support binding to properties of an object, but not fields?
  2. Auto Increment after delete in MySQL
  3. Why are my fields initialized to null or to the default value of zero when I’ve declared and initialized them in my class’ constructor?
  4. MySQL AUTO_INCREMENT does not ROLLBACK
  5. How do I assign by “reference” to a class field in C#?
  6. Mongoose auto increment
  7. How to convert primary key from integer to serial?
  8. How to handle fragmentation of auto_increment ID column in MySQL
  9. Prevent auto increment on MySQL duplicate insert
  10. How to fill in the “holes” in auto-increment fields?
  11. Reordering checkout fields in WooCommerce 3
  12. How to empty input field with jQuery
  13. Get list of all input objects using JavaScript, without accessing a form object
  14. Set field value with reflection
  15. C# constructing parameter query SQL – LIKE %
  16. What are the overheads of using AUTOINCREMENT for SQLite on Android?
  17. Overriding fields or properties in subclasses
  18. Changing primary key int type to serial
  19. Create autoincrement key in Java DB using NetBeans IDE
  20. SQL server identity column values start at 0 instead of 1
  21. Incompatible wire encryption levels requested on client and server with Firebird ado.net provider
  22. How does MySQL Auto Increment work?
  23. postgres autoincrement not updated on explicit id inserts
  24. How to get last inserted row ID from WordPress database?
  25. How to export / dump a MySql table into a text file including the field names (aka headers or column names)
  26. GDS Exception. 335544421. connection rejected by remote interface
  27. How to remove a field from the parent Form in a subclass?
  28. How can I improve performance of Field.set (perhap using MethodHandles)?
  29. make an ID in a mysql table auto_increment (after the fact)
  30. Create unique autoincrement field with mongoose [duplicate]

Leave a Comment