Auto Increment Primary Key for InterBase

A lot of databases has a column autoincrement attribute or an autoincrement data type. They are used to create unique identifiers for a column. In InterBase, you must use a Generator in combination with a Trigger to get an autoincrement column. DeZign for Databases will automate the creation of an autoincrement column for you.

To make an InterBase autoincrement column in DeZign for Databases:

1. Select Dictionary | Generators from the application menu.
2. In the Generators/Sequences dialog, add a new generator (let's say Sequence_1). Press OK to save your changes.
3. Go to the Entity dialog of the entity which owns the Attribute(column) you want to make autoincrement.
4. Select the Attribute in the list of attributes.
5. Select a numeric data type for the Attribute (NUMERIC, INTEGER).
6. Go to tab "Advanced" in the property editor at the bottom of the list of attributes.
7. Select "Sequence_1" in the Apply sequence on attribute field.
8. A "autoincrement" trigger will be created now automatically. This trigger will use the selected Generator/Sequence. Go to tab Triggers to see the generated trigger.

The generated trigger code:

CREATE TRIGGER %triggername% FOR %tablename%
BEFORE INSERT AS BEGIN
  IF (NEW.%columnname% IS NULL) THEN NEW.%columnname% = GEN_ID(%seqname%, 1);
END
^

%seqname% will be replaced with the name of the sequence when you generate the database.

%triggername% will be replaced with the name of the trigger when you generate the database.

%columnname% will be replaced with the name of the associated column when you generate the database.

Note that the sequence can be reused. You can use the same sequence on multiple columns if you want.

The generated trigger code is generated based on the template settings in the interbase definition file (interbase[followed by the interbase version number].def). The definition files are located in the Definitions subdirectory of DeZign for Databases' installation directory.


See also: About Primary Keys and auto increment columns