Blog do André

Por vezes, existe a necessidade de guardar um identificador numérico de forma sequencial para um determinado registo numa base de dados. Isto pode ser alcançado de várias formas, entre as quais o caso em que o ID numérico é calculado on the fly (em tempo de execução), por exemplo ordenando todos os registos por ordem de adição. No entanto, muita gente resolve este problema mudando a chave primária. Neste artigo, quero mostrar algumas implicações desta abordagem, e mostrar alternativas menos prejudiciais.

Um novo problema

Porque não se deve usar um campo chave primária para esta tarefa? Bem, porque isto vai fazer com que tenhamos mais trabalho a gerir as chaves, coisa que o SGBD pode tratar internamente. Por exemplo, quando definimos a chave primária como auto incremento, estamos a deixar sobre responsabilidade do SGBD a geração automática do identificador do registo. Assim, se apagarmos alguns registos, nunca será gerado um identificador que já foi usado anteriormente. Isto é importante, porque garante que nenhum registo antigo que tenha como chave estrangeira este identificador irá ser incorretamente associado com o novo registo. Portanto, o ideal será não mexer na chave primária para este efeito.

Ok, não mexo na chave primária. Como faço então?

No caso se não ser possível/viável gerar estes códigos em tempo de execução (uma vez que são sequenciais, tendo o primeiro termo da sequência, deve ser fácil obter os seguintes numa listagem completa), pode-se criar um campo adicional na tabela, no qual será colocado o valor sequencial para cada registo. Por exemplo:

Desta forma, se eliminarmos um registo, por exemplo, com o ID “2″, ficaremos com a seguinte situação:

O identificador não é alterado, mas o campo de ordem é alterado para manter a sequência. Vamos ver agora o caso de adicionar:

Foi adicionado um novo registo. O identificador não usa nenhum código que tenha sido usado antes (para evitar ligação a referências perdidas noutras tabelas), e dessa forma, não garante a sequência pretendida. No entanto, o campo “ordem” mantém essa sequência, atualizando todos os registos.

Parece bem. Como implementar isto?

A implementação desta solução passa por criar um campo dedicado para a sequência, e ajustar a ordem dos membros a cada eliminação ou adição de dados. Isto é feito com recurso a uma ou duas queries adicionais para cada caso. Vejamos o caso de adicionar registos:

SELECT MAX(Ordem) FROM tabela
INSERT INTO tabela (Ordem, Registo) VALUES (valor + 1, 'Novo registo')

Em que “valor” é o valor obtido da primeira query. Tenham em atenção o caso em que a tabela não tem registos, o max irá devolver NULL: nessa situação, devem assumir o valor como 0, para ser incrementado no campo Ordem.

Vamos ver o caso de eliminar registos: se eliminarmos o último registo, não há problema pois os registos anteriores mantêm-se ordenados; mas se apagarmos um registo a meio da tabela, vamos ficar com um “buraco” na sequência. Isso resolve-se da seguinte forma:

SELECT Ordem FROM tabela WHERE Id = x
DELETE FROM tabela WHERE Id = x
UPDATE tabela SET Ordem = Ordem - 1 WHERE Ordem > valor

Em que “x” é o identificador do registo a ser eliminado e “valor” é o número de Ordem correspondente. Desta maneira, atualizamos todos os registos superiores para manterem a ordem (subtraindo o seu valor de Ordem atual).

Quando usar o campo de Ordem e quando usar o ID?

A questão agora é quando usar o identificador ou o campo de ordem. O ID deve servir principalmente para identificar registos entre tabelas: por não serem sequenciais, o SGBD garante que identificadores utilizados anteriormente não são novamente utilizados. Já o campo de Ordem pode ser usado para outros fins exigidos pela camada de aplicação do programa, contudo, não deve ser usados internamente para referenciar outros registos: o campo ID serve para  esse fim, pois está a sua gestão está a cargo do SGDB.

Concluindo, a intenção do campo Ordem é evitar mexer no campo ID, deixando a gestão deste último para o SGBD. Já o campo Ordem está sobre vosso controlo, sendo possível alterá-lo para manter uma certa sequência. Como nota, se tiverem muito acessos concorrentes à base de dados, pode revelar-se útil colocar todos os passos de uma inserção ou eliminação dentro de uma transação, para não haver conflitos com o vosso novo campo de ordem.