CODEWITHSUNDEEP
sqlfirebird
João Paulo Santos
João Paulo Santos

Reputation: 13

SQL Primary Key Increment

How can I have a primary key that auto increments? for example: when I insert something at the field name, the field id should be assign a value that is +1 that the last one.

id, name
 1, test1
 2, test2

so I insert the name something like testX and the id should be auto assigned as 3

how can i do that?

Upvotes: 0

Views: 1550

Answers (2)

Val Marinov
Val Marinov

Reputation: 2755

In addition to answer of ain, you can use generator like :

Create a generator :

CREATE GENERATOR <name>;
SET GENERATOR <name> TO <value>;

Getting the current value of generator:

SELECT GEN_ID( <GeneratorName>, 0 ) FROM RDB$DATABASE;

Generating the next value 

 SELECT GEN_ID( <GeneratorName>, 1 ) FROM RDB$DATABASE;

Using generator in trigger

CREATE OR ALTER trigger <trigger_name>_bi for <table_name>
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(my_generator,1);
end

If you want numeration without holes you have to be very careful.

Generators/Sequences are non-transactional.
Once you get a value if your operation fails, you will have a hole.

Upvotes: 5

ain
ain

Reputation: 22759

If you're using Firebird 3 then you can use the identity column feature:

create table T (
  id integer generated by default as identity primary key,
  ...
);

If you're using some older version of Firebird, then using an sequence with trigger can be used to achieve the autoincrement:

create table T (
  id integer primary key,
  ...
);

CREATE SEQUENCE t_seq;

create trigger t_gen_id
  active before insert
  on T
as
begin
  if(new.id is null)then new.id = next value for t_seq;
end;

Since Firebird 2 the returning clause is supported which is very handy to get the id value generated on the server side.

Upvotes: 4

Related Questions