Как создать и заполнить искусственные первичные ключи (часто необходимо для репликации)

Em Português: Como criar e preencher chaves primárias artificiais (geralmente necessárias para replicação)

Многие люди, которые пытались настроить нативную репликацию в Firebird, столкнулись с неожиданной проблемой - отсутствием первичного или уникального ключа для таблиц, которые они хотят реплицировать.

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

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

Некоторые люди также ошибочно полагали, что первичный ключ можно заменить комбинацией ограничения CHECK и/или триггера Before Insert с оператором «Select…. Where Exists» для проверки существования того же ключа. Это неверно, такая схема не дает гарантии уникальности значения.

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

Альтернативой может быть создание искусственных первичных ключей: т.е. столбца с автоматически заполняемым значением, со связанной последовательностью (генератором) и триггером. Необходимо создать эти объекты, заполнить значения для существующих записей, создать первичный ключ, а затем создавать уникальные значения для этого поля для каждой новой записи.

Для этой цели мы создали следующую инструкцию и шаблоны.

Инструкция: как создать искусственные первичные ключи

Обратите внимание: все операции ниже требуют монопольного доступа, без подключенных пользователей!

Нам нужно найти все таблицы без первичных или уникальных ключей. Используйте следующий SQL:

Code

with R
as (select R.RDB$RELATION_NAME
    from RDB$RELATIONS R
    where R.RDB$RELATION_TYPE = 0 and
          R.RDB$SYSTEM_FLAG is distinct from 1),
B
as (select distinct R.RDB$RELATION_NAME, RI.RDB$INDEX_NAME, RI.RDB$UNIQUE_FLAG, RI.RDB$INDEX_INACTIVE
    from RDB$INDICES RI
    join RDB$RELATIONS R on RI.RDB$RELATION_NAME = R.RDB$RELATION_NAME
    where R.RDB$SYSTEM_FLAG is distinct from 1 and
          RI.RDB$UNIQUE_FLAG = 1 and
          RI.RDB$INDEX_INACTIVE is distinct from 1)
select R.*
from R
left join B on R.RDB$RELATION_NAME = B.RDB$RELATION_NAME
where B.RDB$RELATION_NAME is null;

Сохраните вывод вышеуказанного SQL в файл, а затем присвойте каждой таблице номер (##### в шаблоне).

В результате у вас будет список с 2 столбцами, примерно такой:

Code

### _TABLE_
001 Table1
002 Table2
..
099 Table99

Для каждой таблицы в списке вам нужно подготовить SQL-скрипт, используя следующий шаблон - замените ##### и _Table_ на фактический номер и имя таблицы из скрипта:

Code


create sequence RPL_#####_SEQ;   -- создать последовательность/генератор
alter sequence RPL_#####_SEQ restart with -1;   -- убедиться, что она начинается с 0

---- добавить столбец в таблицу со значением по умолчанию = -1.
---- Имя столбца: RPL_#####_ID
alter table _TABLE_
 add RPL_#####_ID integer default -1 not null;

---- изменить терминатор, чтобы можно было выполнить этот скрипт в isql
set terminator ^;

---- создать триггер Before Insert для автоматического заполнения новых значений
create trigger RPL_#####_TRG
  before insert
  on _TABLE_
as
 begin
  if (new.RPL_#####_ID is null) then
   new.RPL_#####_ID = next value for RPL_#####_SEQ;
 end;
^
set terminator ;^

---- создать или изменить последовательность для заполнения существующих записей - начиная с -2B
create or alter sequence tmp_seq start with -2000000000;
commit;

---- собственно обновление. Время выполнения этой операции зависит от количества записей в _TABLE_

update _TABLE_
 set RPL_#####_ID = next value for tmp_seq
where RPL_#####_ID = -1;
commit;

---- удалить значение по умолчанию -1 из _Table_
alter table _TABLE_
 alter RPL_#####_ID
  drop default;

---- Наконец, добавить первичный ключ

alter table _TABLE_
 add constraint RPL_#####_PK
  primary key (RPL_#####_ID)
   using index RPL_#####_IDX;
commit;

Выполните скрипт в isql - откройте и скопируйте-вставьте или isqil -i script_name.sql
Возможно, потребуется удалить искусственный первичный ключ и связанные объекты, например, из-за изменений от поставщика.

Для удаления ключей и триггеров у нас есть следующий шаблон скрипта.

Code

alter table _TABLE_ drop constraint RPL_#####_PK;
drop trigger RPL_#####_TRG;
drop sequence RPL_#####_SEQ;
alter table _TABLE_drop RPL_#####_ID;
commit;

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

Часто задаваемые вопросы

1. Следует ли использовать первичный ключ или уникальный ключ для репликации?

Можно создать уникальный ключ как искусственный ключ для репликации. В целом, уникальный ключ может быть построен на поле, допускающем NULL, поэтому такой ключ будет допускать хранение значений NULL; и если в таблице будет более одной записи с NULL в уникальном ключе, может возникнуть неоднозначный конфликт на стороне реплики, когда обновление или удаление прибудет в базу данных реплики.

Поэтому мы рекомендуем создавать искусственный первичный ключ или уникальный ключ с полем NOT NULL для целей репликации.

2. Как исключить таблицы из репликации?

Чтобы исключить конкретные таблицы, добавьте в конфигурацию репликации следующий фильтр:

Code

exclude_filter=TESTTABLEWITHOUTPK|TESTTABLE2

или можно использовать подстановочные знаки, как в Similar To

Code

exclude_filter=TEST%

Чтобы увидеть, какие таблицы будут исключены фильтром, используйте следующий оператор:

Code

select rdb$relation_name from rdb$relations where rdb$relation_name similar to 'TEST%';

RDB$RELATION_NAME
========================================================
TESTTABLEWITHOUTPK
TESTTABLE2

Также в Firebird 4 вы можете использовать SQL-команду для исключения таблицы из публикации:

Code

ALTER DATABASE EXCLUDE MyTable1 FROM PUBLICATION