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 (disponível no vanilla Firebird 4 e HQbird v2.5, 3.0, 4.0) se depararam com o problema inesperado - ausência de chave primária ou única para as tabelas que desejam replicar.

Apesar do fato de que ter restrição de chave primária para a tabela é o requisito básico da teoria relacional, vimos muitos bancos de dados onde as tabelas a serem replicadas não tinham chaves primárias ou únicas.

Normalmente os motivos da ausência da chave primária são bem simples: desenvolvedores esqueceram de criar a chave primária ou consideraram a tabela criada como “temporária”, mas ela ficou no esquema, passou a fazer parte da lógica de negócio, 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 “Select…. Where Exists” para verificar a existência da mesma chave. Isso não é verdade, tal esquema não dá garantia de 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, 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 chave primária artificial: ou seja, coluna com valor preenchido automaticamente, com sequência associada (gerador) e gatilho. É necessário criar esses objetos, preencher os valores dos registros existentes, criar a chave primária e, então, criar valores únicos para esse campo a cada novo registro.

Para este propósito, criamos as seguintes instruções e modelos.

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

Atenção: todas as operações abaixo requerem acesso exclusivo, sem usuários conectados!

Precisamos encontrar todas as tabelas sem chaves primárias ou exclusivas. 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 em algum arquivo e atribua a cada tabela o número (##### no modelo). Como resultado, você terá uma lista com 2 colunas, algo assim:

Code

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

Para cada tabela na lista, você precisa preparar o script SQL, usando o seguinte modelo - substitua ##### e _Table_ 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;  -- certifique-se de que começa do 0

--- add column to the table with default value = -1.
--- The name of the column is RPL_#####_ID
alter table _TABLE_
 add RPL_#####_ID integer default -1 not null;

---- altere 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 _TABLE_
as
 begin
  if (new.RPL_#####_ID is null) then
   new.RPL_#####_ID = next value for RPL_#####_SEQ;
 end;
^
set terminator ;^

---- criar ou alterar a sequência para preencher os registros existentes - começando em -2B

create or alter sequence tmp_seq start with -2000000000;
commit;

---- realmente atualizando.
---- O tempo para realizar esta operação depende do número de registros em _TABLE_

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

---- remova o Default -1 da _Table_

alter table _TABLE_
 alter RPL_#####_ID
  drop default;

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

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

É possível que seja necessário descartar a chave primária artificial e os objetos associados, por exemplo, devido a alterações do fornecedor. Para descartar as chaves e os gatilhos, temos o seguinte modelo de script.

Code

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

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

Perguntas Frequentes

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

Teoricamente, é possível criar uma chave única, mas a chave única pode ser criada no campo sem NOT NULL, e permite armazenar o valor NULL e, se houver mais de um registro com NULL na tabela, pode haver conflito no lado da réplica quando a atualização ou exclusão chegar ao banco de dados da réplica .

Portanto, recomendamos criar chaves primárias ou chaves unicas com base no campo NOT NULL.

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 a tabela da publicação:

Code

ALTER DATABASE EXCLUDE MyTable1 FROM PUBLICATION