Estou adicionando um widget personalizado a uma instância do Drupal 6.x e MySQL 5.5 e encontrei um problema ao atualizar as linhas.
Eu tenho uma tabela de ingredientes de receita em que vários ingredientes estão vinculados a uma única receita por id de nó (nid) e id de versão (vid).
A chave primária é vid, nid & order.
Os campos vid e nid estão relacionados aos campos nid e vid da receita.
O esquema da tabela é:
+-----------------+------------------+
| Field | Type |
+-----------------+------------------+
| vid | int(10) unsigned |
| nid | int(10) unsigned |
| order | int(10) unsigned |
| name | varchar(255) |
| unit_of_measure | varchar(32) |
| quantity | int(10) unsigned |
+-----------------+------------------+
O problema surge ao tentar reordenar os ingredientes.
Por exemplo:
+-----+-----+-------+---------+-------------------+----------+
| vid | nid | order | name | unit_of_measure | quantity |
| 5 | 1 | 1 | Chicken | Lb | 1 |
| 5 | 1 | 2 | Rice | Cup | 2 |
| 5 | 1 | 3 | Thyme | Tbsp | 3 |
+-----+-----+-------+---------+-------------------+----------+
Quero mover o tomilho para o topo da lista, mas não posso alterar a ordem do tomilho para 1, pois a chave primária já existe (frango). Não consigo mover o Frango para o pedido 2 porque o Arroz já está lá, etc...
Minha posição é que devemos adicionar um campo int exclusivo de incremento automático que será a única chave primária. O que nos permitirá reordenar as linhas sem incidentes com a possibilidade de que duas linhas acabem com o mesmo nid, vid e order.
A posição de meus colegas de trabalho era que adicionar um campo int de incremento automático exclusivo é um design ruim, porque nunca deve haver duas linhas diferentes com o mesmo vid, nid e ordem. Mas seguindo essa crença, existem duas maneiras de implementar uma reordenação das linhas
- Atualize a ordem de cada linha com um número grande (ou seja, 1001, 1002, 1003) para que a ordem original não seja mais conflitante e, em seguida, atualize cada linha com os valores de ordem corretos (1, 2, 3).
- Exclua cada linha que tenha o mesmo nid e vid e insira todas as linhas novamente na ordem correta.
Do ponto de vista do banco de dados, qual é a abordagem correta?
O problema é semelhante a esta pergunta (SO):
Como trocar valores de duas linhas no mysql sem violar a restrição exclusiva?
Infelizmente, a resposta é a mesma. Devido à forma como o MySQL verifica
UNIQUE(e chaves primárias e estrangeiras) as restrições, ou seja, linha por linha e não no final das instruções ou no final das transações, trocar ou reorganizar valores que fazem parte de restrições exclusivas não pode ser feito facilmente.Eu iria com a sua 1ª sugestão:
1. Atualize a ordem de cada linha com um número grande (ou seja, 1001, 1002, 1003) para que a ordem original não seja mais conflitante e, em seguida, atualize cada linha com os valores de ordem corretos (1, 2, 3).
Dessa forma, você pode ter as instruções mínimas (2) necessárias para fazer a atualização e qualquer outra conexão que leia a tabela entre as 2 instruções ainda lerá um estado consistente de valores.
Por que não recriar a chave primária como (nid, vid) e criar um novo índice na coluna "order" (apenas para recuperação rápida / cláusula ORDER BY).
--> O pior que pode acontecer é ter 2 ingredientes com o mesmo valor de "pedido", mas a lógica de troca de pedido já deve estar definida corretamente na aplicação.