Firebird DB: создание простого primary key auto increment

Проблема

В Firebird по-умолчанию невозможен автоинкремент первичных ключей, как это, например, возможно в MySQL.

Для реализации auto increment для первичного ключа будем использовать триггеры.

Primary Key

Сперва опишу процесс создания первичного ключа. Первичный ключ, он же primary key или PK, можно описать непосредственно при создании новой таблицы, либо добавить к существующей.

PK при создании таблицы

 CREATE TABLE table_example
(
    id INTEGER NOT NULL PRIMARY KEY
);

В новой таблице table_example мы создаем первичный ключ id. Что примечательно, он должен быть обязательно not null.
Тут все просто. Несколько сложнее дела обстоят с добавлением первичного ключа к сущетсвующей таблице.

PK в уже существующей таблице

Сперва нужно добавить обычное поле в таблицу:

ALTER TABLE table_example ADD id INTEGER NOT NULL;

Очевидно, если в уже таблице есть данные, то новому полю нужно присвоить какие-либо значения. Делаем update таблицы и меняем значение поля id для каждой записи таким образом, чтобы оно было уникальным.
Осталось сделать поле первичным ключом:

ALTER TABLE table_example ADD CONSTRAINT constraint_name PRIMARY KEY (id)

Здесь мы добавляем констреинт с именем constraint_name, в котором описываем первичный ключ. Этот запрос отработает, если в таблице не было значений, либо все значения в поле id были уникальными.

Auto increment

Теперь нужно добавить к нашему первичному ключу авто увеличение значения.

Для этого нужно:

• Создать свой генератор, в котором будет храниться текущее значение PK
• Написать триггер для таблицы, который будет присваивать PK в новой записи уникальное значение, большее значения, хранящегося в генераторе на какой-либо число.
•  

Генератор

Создаем новый генератор:

CREATE GENERATOR generator_name;

Триггер

Добавляем триггер для нашей таблицы table_example, используя созданный генератор generator_name:

SET TERM ^ ;
CREATE TRIGGER trigger_name FOR table_example ACTIVE
BEFORE INSERT POSITION 1
AS
BEGIN
    if (new.id is null ) then
    new.id = gen_id (generator_name, 1);
END^
SET TERM ; ^

В данном примере триггер будет отрабатывать до вставки новой записи в таблице, присваивая значению id (если оно не указано явно) значение, на единицу большее, чем хранится на тот момент в генераторе.
Тут я выделил два момента:

• POSITION 1. Значение позиции определяет, с какого численного значения генерировать очередное значение для PK. Для пустой таблицы, само собой, генерацию лучше производить с единицы, как и указано. Но если мы добавляли PK в существующую таблицу с имеющимися данными, то после добавления и апдейта PK у нас уже имеет некоторые значения. По-хорошему, это промежуток значений [1, N], где N - количество записей. В таком случае позицию нужно указать, как POSITION N+1.
  Так, если макисмальное значение PK было 3, то в значение позиции пойдет число 4.
• Логика триггера. Код триггера, описанный между BEGIN и END, собственно, и реализует логику. Здесь используется системная функция gen_id, увеличивающая значение генератора, переданного первым аргументом, на значение, переданное вторым аргументом. В данном случае мы инкрементим на единицу.

Следует отметить, код логики триггера имеет свой синтаксис, а, следовательно, свои разделители. В данном случае, точку с запятой. Так как точка с запятой в свою очередь разделяет запросы друг от друга, чтобы не было путаницы, для запросов делают переход от точки с запятой на "крышечку" до начала запроса:

SET TERM ^ ;

  И обратно - после:

SET TERM ; ^