Firebird — Как создать столбец с autoicrement?

Добавить комментарий

Задача: Нужно создать таблицу со столбцом, в котором значение будет увеличиваться по мере добавления данных. (так званное autoicrement поле)

Инструменты: Firebird 2+

Решение: В Firebird нельзя полю установить свойство autoincrement как например в СУБД MSSQL или MySQL. Для этого нужно создать генератор (GENERATOR/SEQUENCES) и триггер на событие BEFORE INSERT.

Допустим у нас есть таблица table1 с полями id и field1. Нужно сделать столбец id — autoincrement

CREATE TABLE Table1
(
  Id INTEGER NOT NULL,
  Field1 VARCHAR(255) NOT NULL
);Code language: PHP (php)

Чтобы сделать автоинкремент для столбца id, нам нужно создать триггер BEFORE INSERT, который будет устанавливать новое значение каждый раз, когда добавляется новая запись. Для генерации нового уникального значения, мы воспользуемся генератором (он же generator/sequence):

CREATE GENERATOR Gen_Table1_Id;
SET GENERATOR Gen_Table1_Id TO 0;

Оператор SET GENERATOR используется для установки начального значения последовательности. Поскольку мы установили его в 0, при попытке получения первой записи, мы получим значение 1.

Теперь создаем триггер:

SET TERM ^;
	CREATE TRIGGER Table1_BI_TR FOR Table1
	ACTIVE BEFORE INSERT POSITION 0
	AS
	BEGIN
	 if (NEW.Id is NULL) then NEW.Id = GEN_ID(Gen_Table1_Id, 1);
	END^
SET TERM ;^Code language: PHP (php)

Проверка, имеет ли NEW.ID значение NULL, является распространенным способом предотвращения получения увеличивающихся значений при копировании данных из другой базы данных или импорте из другого источника данных. Конечно же это можно сделать разными способами, но это наиболее простой подход: если значение для столбца не указано — автоинкрементируем его.

Если вам интересно, зачем нужны эти операторы SET TERM: это потому, что многие инструменты используют точку с запятой для разделения операторов, а код триггера содержит точку с запятой, поэтому необходимо изменить символ завершения оператора.

Все это может показаться неудобным, но это правильный способ сделать это в многопользовательской среде. Кстати, большинство инструментов администрирования с графическим интерфейсом имеют возможность автоматически сгенерировать этот код, поэтому это не проблема.

Настало время проверить наши изменения. Для упрощения, вернем сразу же результат при выполнении операции вставки (Insert). Для этого воспользуемся ключевым словом RETURNING, которое появилось в Firebird 2.1 и выше.

INSERT INTO Table1(Field1) VALUES('Value 1') RETURNING id;
Code language: JavaScript (javascript)

Если вы выполните такой оператор в своем инструменте администрирования, вы получите набор результатов с одной строкой с идентификатором столбца, содержащим недавно вставленный идентификатор записи. В нашем случае это 1.

Что делать если у меня старая версия Firebird?

Если вы используете старую версию Firebird, вы должны сначала получить значение с помощью GEN_ID, а затем использовать его в операторе INSERT. Чтобы прочитать значение генератора, вы можете использовать некую таблицу с одной записью, например RDB$DATABASE:

select GEN_ID(GEN_Table1_ID, 1) from RDB$DATABASE;
Code language: JavaScript (javascript)

Как добавить поле Autoincrement при использовании Delphi и Zeos?

Если вы используете Delphi и Zeos (или подобную библиотеку), вы можете использовать компонент ZSequence для автоматического чтения GEN_ID. В ZTable отметьте поле autoincrement как SequenceField и установите соответствующий объект ZSequence, и все это будет работать автоматически.

Как мигрировать приложение с MySQL в Firebird?

Если вы переносите существующее приложение MySQL на Firebird, вам может потребоваться получить LAST_INSERT_ID. Это последний идентификатор вставки при подключении. Чтобы имитировать поведение MySQL в Firebird, вы можете использовать контекстные переменные. После того, как вы получите идентификатор в триггерах перед вставкой, сохраните его в переменной контекста с областью подключения:

CREATE TRIGGER
...
new.autoinc_field = gen_id(generator_for_autoinc_field, 1);
RDB$SET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID', new.autoinc_field);
...Code language: PHP (php)

Позже вы можете получить LAST_INSERT_ID с помощью EXECUTE BLOCK, если вы запустите Firebird 2.1 или выше. Для более ранних версий Firebird и даже для 2.1, если вам нравится некоторая элегантность, вы можете создать хранимую процедуру:


CREATE PROCEDURE LAST_INSERT_ID RETURNS (ID BIGINT) AS
BEGIN
  ID = RDB$GET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID');
  suspend;
ENDCode language: PHP (php)

В коде нужно будет заменить вызов для получение id на:

SELECT ID FROM LAST_INSERT_ID;

Добавить комментарий Отменить ответ