Jak tworzyć i wypełniać sztuczne klucze główne (często potrzebne do replikacji)

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

Muitas pessoas que tentaram configurar a replicação nativa no Firebird enfrentaram o problema inesperado - a ausência de chave primária ou única para as tabelas que desejam replicar.

Apesar de ter uma restrição de chave primária na tabela ser o requisito mais básico da teoria relacional, vimos muitos bancos de dados onde as tabelas a serem replicadas não possuíam chaves primárias ou únicas.

Geralmente, os motivos para a ausência de chave primária são muito simples: os desenvolvedores esqueceram de criar a chave primária ou consideraram a tabela criada como “temporária”, mas ela permaneceu no esquema, tornou-se parte da lógica de negócios, etc.

Poucas pessoas também acreditaram erroneamente que a chave primária pode ser substituída por uma combinação de restrição CHECK e/ou trigger Before Insert com a instrução “Select…. Where Exists” para verificar a existência da mesma chave. Isso não é verdade, tal esquema não garante a unicidade do valor.

A melhor maneira de resolver o problema descrito é criar uma chave primária para o(s) campo(s) existente(s) que identifica(m) exclusivamente cada registro na tabela. No entanto, isso pode ser uma tarefa não trivial e demorada, especialmente se o banco de dados for criado por um fornecedor terceirizado ou se tiver uma estrutura complexa.

A alternativa poderia ser a criação de chaves primárias artificiais: ou seja, uma coluna com valor preenchido automaticamente, com uma sequência (gerador) e trigger associados. É necessário criar esses objetos, preencher os valores para os registros existentes, criar a chave primária e, em seguida, criar valores únicos para este campo para cada novo registro.

Para este propósito, criamos a seguinte instrução e modelos.

Instrução: como criar chaves primárias artificiais

Observe: todas as operações abaixo exigem acesso exclusivo, sem usuários conectados!

Precisamos encontrar todas as tabelas sem chaves primárias ou únicas. Use o seguinte 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;

Salve a saída do SQL acima em um arquivo e, em seguida, atribua a cada tabela o número (##### no modelo).

Como resultado, você terá uma lista com 2 colunas, algo assim:

Code

### _TABELA_
001 Tabela1
002 Tabela2
..
099 Tabela99

Para cada tabela da lista, você precisa preparar o script SQL, usando o seguinte modelo - substitua ##### e _Tabela_ pelo número real e nome da tabela do script:

Code


create sequence RPL_#####_SEQ;   -- criar sequência/gerador
alter sequence RPL_#####_SEQ restart with -1;   -- garantir que comece em 0

---- adicionar coluna à tabela com valor padrão = -1.
---- O nome da coluna é RPL_#####_ID
alter table _TABELA_
 add RPL_#####_ID integer default -1 not null;

---- alterar o terminador, para poder executar este script no isql
set terminator ^;

---- criar trigger Before Insert para preencher automaticamente novos valores
create trigger RPL_#####_TRG
  before insert
  on _TABELA_
as
 begin
  if (new.RPL_#####_ID is null) then
   new.RPL_#####_ID = next value for RPL_#####_SEQ;
 end;
^
set terminator ;^

---- criar ou alterar sequência para preencher registros existentes - começando de -2B
create or alter sequence tmp_seq start with -2000000000;
commit;

---- atualização real. O tempo para executar esta operação depende do número de registros em _TABELA_

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

---- remover o padrão -1 da _TABELA_
alter table _TABELA_
 alter RPL_#####_ID
  drop default;

---- Finalmente, adicionar a chave primária

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

Execute o script no isql - abra e copie/cole ou isqil -i nome_do_script.sql
É possível que seja necessário remover a chave primária artificial e os objetos associados, por exemplo, devido a alterações do fornecedor.

Para remover as chaves e triggers, temos o seguinte modelo de script.

Code

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

Como você pode ver, ambos os scripts (para criar chaves e remover) alteram os metadados, o que significa que devem ser executados em modo exclusivo, quando nenhum usuário estiver conectado.

Perguntas Frequentes

1. Devemos usar chave primária ou chave única para a replicação?

É possível criar uma chave única como chave artificial para replicação. Em geral, uma chave única pode ser construída em um campo anulável, então tal chave permitiria armazenar valores NULL; e se houver mais de um registro com NULL na chave única na tabela, pode haver conflito ambíguo no lado da réplica quando uma atualização ou exclusão chegar ao banco de dados da réplica.

Portanto, recomendamos criar uma chave primária artificial ou chave única com campo NOT NULL para fins de replicação.

2. Como excluir tabelas da replicação?

Para excluir tabelas específicas, adicione à configuração de replicação o seguinte filtro:

Code

exclude_filter=TESTTABLEWITHOUTPK|TESTTABLE2

ou, é possível usar curingas como em Similar To

Code

exclude_filter=TEST%

Para ver quais tabelas serão excluídas pelo filtro, use a seguinte instrução:

Code

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

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

Além disso, no Firebird 4 você pode usar o comando SQL para excluir tabela da publicação:

Code

ALTER DATABASE EXCLUDE MyTable1 FROM PUBLICATION