Использование генераторов для создания уникальных идентификаторов строк

Зачем вообще нужны идентификаторы (ID)?

Ответ на этот вопрос выходит далеко за пределы темы этой статьи. Если вы не видите необходимости иметь общий, уникальный «маркер» (handle) для каждой строки внутри таблицы, или вам вообще не нравится идея «бессмысленных» или «суррогатных» (искусственных) ключей, вам, вероятно, лучше пропустить этот раздел...

Один для всего или один для каждого?

Хорошо... Итак, вы хотите использовать идентификаторы. { примечание автора: мои поздравления! :-) }

Основной, наиболее важный выбор, который вам нужно сделать, - это использовать единый генератор для всех таблиц или один генератор для каждой таблицы. Это только ваш выбор, но примите во внимание следующие размышления.

В подходе «один для всех» вы:

+ нуждаетесь только в одном генераторе для всех ваших ID;
+ имеете одно целочисленное значение, которое не только однозначно идентифицирует строку внутри самой таблицы, но и внутри всей базы данных;
- имеете меньшее количество значений ID на таблицу (на самом деле это не проблема для 64-битных генераторов...);
- будете иметь дело с большими значениями ID, даже если, например, просматриваете таблицу с горсткой записей;
- вероятно, будете видеть пробелы в последовательности ID каждой таблицы, так как значения генератора распределены между всеми таблицами.

В подходе «один для каждой» вы:

- должны создавать генератор для каждого идентификатора таблицы вашей базы данных;
- всегда должны комбинировать ID и имя таблицы для уникальной идентификации строки в таблице;
+ имеете простой и надежный «счетчик вставок» для таблицы;
+ имеете хронологическую последовательность для таблицы: если вы найдете пробелы в последовательности ID таблицы, то они появилась либо из-за удаления (DELETE), либо при неудачной вставке (INSERT).

Можно ли использовать значения генератора повторно?

В действительности – да, технически это возможно. В реальности – НЕТ, вы не должны. Никогда. Ни при каких условиях. Не только потому, что это может нарушить красоту хронологической последовательности (вы не сможете более судить о «возрасте» строки, взглянув на ее ID), а еще и потому, что повышается риск появления сильной головной боли, которую вы при этом получите. Более того, это абсолютно противоречит всей концепции уникального идентификатора строки.

Так что если у вас нет веских причин для повторного использования значений генератора и хорошо продуманного механизма, который безопасно сделает эту работу в многопользовательской/многотранзакционной среде, НЕ ДЕЛАЙТЕ ЭТОГО!

Генераторы для идентификации, или автоинкрементные поля

Указание ID для вновь вставляемой записи (в терминах уникальности - «серийный номер») легче всего выполнить с помощью генератора и триггера «перед вставкой» (Before Insert trigger), как мы увидим в этом разделе. Предположим, что у нас есть таблица TTEST с колонкой ID, объявленной как Integer. Имя нашего генератора: GIDTEST.

Триггер Before Insert, версия 1

CREATE TRIGGER trgTTEST_BI_V1 for TTEST
active before insert position 0
as
begin
  new.id = gen_id(gidTest, 1);
end

Проблемы этого триггера (версии 1):

Он правильно делает свою работу, но он также «тратит» значение генератора в том случае, когда ID уже указан в операторе INSERT. Так что более эффективно было бы присваивать значение, когда ничего не было указано в INSERT:

Триггер Before Insert, версия 2

CREATE TRIGGER trgTTEST_BI_V2 for TTEST
active before insert position 0
as
begin
  if (new.id is null) then
  begin
    new.id = gen_id(gidTest, 1);
  end
end

Проблемы этого триггера (версии 2):

Некоторые компоненты доступа имеют «плохую привычку» автоматического заполнения колонок при выполнении INSERT. Если вы явно не указали, они устанавливают значение по умолчанию - обычно это 0 для колонок целочисленного типа. В этом случае приведенный выше триггер не будет работать: он будет обнаруживать, что колонка ID не находится в состоянии NULL, а имеет значение 0, и поэтому не будет генерировать новое значение ID. Вы сможете вставить запись, но только одну: вставка второй записи окончится неудачей. В любом случае, хорошей идеей будет запрет значения 0 в качестве обычного значения ID, чтобы предотвратить любую неоднозначность между NULL и 0. Вы можете использовать, например, специальную строку с ID, равным 0, для хранения записи «по умолчанию» в каждой таблице.

Триггер Before Insert, версия 3

CREATE TRIGGER trgTTEST_BI_V3 for TTEST
active before insert position 0
as
begin
  if ((new.id is null) or (new.id = 0)) then
  begin
    new.id = gen_id(gidTest, 1);
  end
end

Хотя теперь у нас есть понятный, работающий триггер для ID, следующие абзацы объяснят вам, почему вы чаще всего не захотите им пользоваться.

Основная проблема с присваиванием ID в триггере «перед вставкой» состоит в том, что значение генерируется на стороне сервера, после того, как вы отправили оператор добавления с клиента. Очевидно, это означает, что нет безопасного способа для клиента узнать, какой ID был сгенерирован для только что вставленной вами строки.

Вы можете попытаться получить значение генератора на стороне клиента после вставки, но в многопользовательской среде вы не можете быть уверены, что вы получили свой собственный ID строки (из-за вопросов с транзакциями).

Но если вы сначала получите новое значение генератора и передадите его при вставке, вы можете просто получить эту новую запись с помощью «Select ... where ID = <genvalue>», чтобы увидеть, какие умолчания были применены, или какие колонки были изменены триггерами при вставке. Это особенно хорошо работает, поскольку у вас обычно есть уникальный индекс для первичного ключа (Primary Key) по полю ID, а это наиболее быстрый индекс, который только может быть, - у него идеальная селективность и он гораздо меньше, чем индекс для колонки CHAR(n) (для n>8, в зависимости от кодировки и порядка сортировки [collation]).

Из этого получаем вывод:

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

Если ваша база данных «закрыта для SQL» (то есть только ваше собственное приложение может стать источником вставки новых записей), то вы можете отказаться от триггеров, но при этом вы должны всегда получать новое значение генератора из базы данных прежде, чем выполните оператор вставки, и включать полученное значение в этот оператор. То же самое, конечно же, относится для вставок из триггеров и хранимых процедур.