Helpful Information
 
 
Category: Firebird SQL Development
Easiest way to create an auto incrementing column?

Using FB - is there a built in way to create an auto incrementing column? If not, what is the easiest way to do this? I am guessing using a Generator - is this correct?

Thanks

Yes you use a Generator.

For example:

INSERT INTO SALES (PO_NUMBER) VALUES (GEN_ID(generator_name, 1));Or if you need the generator client-side before the insert, write a stored procedure which returns a generated number you can use in your program.
/* This stored procedure returns a generated integer */
CREATE PROCEDURE GEN_BATCH_ID returns (ID_VALUE Integer)
AS
BEGIN
ID_VALUE = gen_id(BATCH_ID_GEN, 1);
SUSPEND;
ENDOr you can insert the record using a stored procedure and return the generated value after the insert.
/* Send this procedure an Employee's name. Get back the Employee's ID number. */
CREATE PROCEDURE EMPLOYEE_INSERT (LAST_NAME VARCHAR(25), FIRST_NAME VARCHAR(25)) returns (EMPLOYEE_ID Integer)
AS
BEGIN
EMPLOYEE_ID = GEN_ID(EMPLOYEE_ID_GEN, 1);

INSERT INTO EMPLOYEE (
EMPLOYEE_ID,
LAST_NAME,
FIRST_NAME)
VALUES (
:EMPLOYEE_ID,
:LAST_NAME,
:FIRST_NAME);
END

Great - thanks dman- i thought it would be by means of the generator - just wasn't sure if there was already something built in to the package or not...

PS - appreciate the great examples ;)

For example:

INSERT INTO SALES (PO_NUMBER) VALUES (GEN_ID(generator_name, 1));

so how do we get the generator_name?? Please help..

You create it, so you know that name.
Note that you can put a trigger to do it for you, i.e. you insert nulls and it adds the value taken from the generator for you.
Find more infos in this document (http://www.geocities.com/firebird_tut/files/Getting_started_with_Firebird.pdf)

Posting here as a reference for others:
Say you have a table like thisCREATE TABLE new_one
(
col1 integer not null,
col2 char(10),
PRIMARY KEY (field1)
);You want col1 to behave like an autoincrement/identity field.
You create a generatorCREATE GENERATOR my_gen_id; (you could set more options).
Now you want it to kick in automatically, just like an autoincrement, no need to explictly call it,
you can achieve this through a trigger:CREATE TRIGGER autoincrementor_id FOR new_one
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.col1 IS NULL) THEN
NEW.col1 = GEN_ID(my_gen_id,1);
END










privacy (GDPR)