Hoe kunstmatige primaire sleutels te creëren en in te vullen (vaak nodig voor replicatie)

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

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

Apesar do fato de que ter uma restrição de chave primária para a tabela é 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, as razões 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 campos existentes que identifiquem 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 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

### _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;   -- garantir que comece em 0

---- adicionar coluna à tabela com valor padrão = -1.
---- O nome da coluna é RPL_#####_ID
alter table _TABLE_
 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 _TABLE_
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 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;

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

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

alter table _TABLE_
 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 script_name.sql
É possível que seja necessário remover a chave primária artificial e 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 _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 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 atualização ou exclusão chegar ao banco de dados da réplica.

Portanto, recomendamos criar 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