Search code examples
sqlfirebirdauto-incrementfirebird-4.0

Setting auto-increment after filling table (Firebird) with existing data (from SQL Server)

I have a table in SQL Server. Its PK, ID, is of type INT and is auto-incremented. The task I have is to get this table in a Firebird database (version 4.0.4). Remarks: we have a website that uses this DB where pages have a host of references to those IDs, so they should be kept as they are.

I'm using simple console program in .NET 6 (C#) to select and insert data using ADO.NET provider. My question is if I should first import data to the Firebird database and then add a generator as auto-increment to that table, considering the fact that generator should use the maximum value of PK of the existing data as its starting value. As far as I understand there at least two things to care about:

  1. If I create auto-increment first and insert data (select without ID column), those IDs will start from 1.
  2. I am not sure what will be the result of that generator's action when it starts from a default 0 value. Suppose there are IDs 2, 4, 5, 8. On next insert it creates 1 as ID. And what will be the outcome of the second one? ++1 gives 2 which is already in use, so we are stuck.

Am I on a correct way? Correct me if I am wrong.

Solution

  • If you would use a sequence (a.k.a. generator) and not modify its current value after a bulk insert which inserts with explicit ID values, then for subsequent inserts, the sequence will generate values 1, 2, 3, 4, etc.. So, if such a value already happens to exist, you'll get duplicate values. What happens depends on whether or not the column has a primary key or unique key constraint: if there is no constraint, a duplicate ID value would be inserted, if there is a constraint, the insert will fail.

    There are multiple ways to go about this. Since Firebird 3.0, Firebird has "real" identity columns, which means you don't have to fiddle with sequences and triggers yourself. The downside is that it is not possible to add an identity column to a table which already has rows, and it is not possible to convert an existing column to an identity column.

    Given you're using Firebird 4.0, I would recommend doing the following:

    1. Create the table with a GENERATED ALWAYS AS IDENTITY column. You can also use GENERATED BY DEFAULT AS IDENTITY, but IMHO you should only do that if you want to be able to override the identity in general:

      create table YOUR_TABLE (
  ID integer generated always as identity constraint PK_YOUR_TABLE primary key,
  -- your other columns
);

    2. If you used GENERATED ALWAYS in the previous step, use the OVERRIDING SYSTEM VALUE in your INSERT statement. That way you can insert an explicit value into a generated always identity column.

      insert into YOUR_TABLE (ID, /* other columns */) overriding system value
  values (2, ...);

      If you used GENERATED BY DEFAULT, you don't need the OVERRIDING clause (allowing the insert to specify the identity column value is default behaviour for generated by default).

    3. After inserting all rows, change the next value of the identity column to the current maximum + 1 (not to the current maximum!).

      • First obtain the maximum + 1 value:

        select 1 + max(ID) as NEXT_VALUE from YOUR_TABLE;

        Make sure you execute this while no other transactions insert records in this table.

      • Use the obtained value in ALTER TABLE .. RESTART WITH ..., say NEXT_VALUE was 1923:

        alter table YOUR_TABLE alter column ID restart with 1923;

      • Alternatively, although formally not supported, you could use PSQL code to run this as one statement:

        set term #;
execute block
as
  declare NEXT_VALUE integer;
begin
  select 1 + max(ID) from YOUR_TABLE into NEXT_VALUE;
  execute statement 'alter table YOUR_TABLE alter column ID restart with ' || NEXT_VALUE;
end#
set term ;#

        NOTE: Use of SET TERM is only needed for interactive execution, like when using ISQL. Firebird itself doesn't support this command. If you execute this, for example, from C#, you just need to execute the execute block ... end (without the #!).

    4. From now on, insert records into this table without specifying the ID column (and if you do, and used generated always you'll get an error unless OVERRIDING SYSTEM VALUE has been specified).

    If you insist on using sequences and triggers, you can do the following:

    1. Create the table with a normal integer column:

      create table YOUR_TABLE_2 (
  ID integer constraint PK_YOUR_TABLE_2 primary key,
  -- your other columns
);

    2. Insert the rows:

      insert into YOUR_TABLE_2 (ID, /* other columns*/) values (2, ...);

    3. After inserting the rows, define the sequence and auto-increment trigger:

      • First obtain the next value:

        select 1 + max(ID) as NEXT_VALUE from YOUR_TABLE_2;

      • Then create the sequence and trigger (assume NEXT_VALUE was 1923):

        create sequence SEQ_YOUR_TABLE_2_ID start with 1923;
set term #;
create trigger GEN_ID_YOUR_TABLE_2 before insert on YOUR_TABLE_2
as
begin
  NEW.ID = next value for SEQ_YOUR_TABLE_2_ID;
end#
set term ;#

        NOTE: This trigger will unconditionally generate an ID. You may want to consider raising an exception if NEW.ID is not null, or to only generate an ID if NEW.ID is null. See my answer here for a conditional alternative.

        Although you could use the PSQL trick to set the sequence value dynamically, I would recommend to first create the sequence (leave off the START WITH clause) and trigger, commit the transaction, and then use an EXECUTE BLOCK with ALTER SEQUENCE ... RESTART WITH ... if you want to go that route.