Como fazer auto_increment em atributo em chave primária múltipla

Trabalho com MySQL, em minhas horas “vagas” e recentemente recebi um desafio para criar um modo de autoincrementar um atributo que pertencia a uma chave primária múltipla.

O desafio apresenta a seguinte situação:

Uma tabela de itens de pedidos, possui dois atributos em sua chave primária: Número do pedido (que chamaremos de ID_Pedido) e número do item do pedido (que chamaremos de IT_Pedido), como autoincrementar o número do ítem de pedido para que iniciasse para cada pedido cadastrado. Ver exemplo abaixo:

+---------+---------+

|ID_Pedido|IT_Pedido|

|---------+---------|

|        1|        1|

|        1|        2|

|        1|        3|

|        2|        1|

|        2|        2|

|        2|        3|

|---------+---------|

Como não é possível usar a cláusula auto_increment no atributo construí uma trigger de inclusão que calcula o valor automaticamente.

Então, vamos ao trabalho!

Passo 1)Primeiro, para nossa demonstração, usando o banco test, criaremos a estrutura da tabela Item de Pedido.

Create Table Item_Pedido (

   ID_Pedido int unsigned not null default 0, /* Número do Pedido ...... */

   IT_Pedido int unsigned not null default 0, /* Número do Item do Pedido*/

   ID_Produto int unsigned not null default 0,/* Identificador do Produto*/

   Primary Key (ID_Pedido, IT_Pedido)

);

Passo 2) Uma vez criado a estrutura da tabela Vamos descrever a lógica que servirá de base para a criação do trigger:

1 – Criar uma variável numérica e atribuir a ela o valor zero como valor inicial;

2 - Buscar o valor máximo do item de pedido (IT_Pedido) para o Pedido (ID_Pedido) que está sendo cadastrado e atribuir o resultado da busca a variável criada;

2 - Comparar o resultado da busca;

2.1 - Se o resultado for nulo, significa que não há registro cadastrado para este Pedido. Então, devemos atribuir o valor 1 a variável criada;

2.2 - Se o resultado for diferente de nulo, então devemos atribuir à nossa variável, o valor encontrado incrementado em 1.

3 – Atribuir a item do pedido o valor contido em nossa variável de apoio.

Passo 3) Escrever o código do trigger:

DELIMITER $$;

DROP TRIGGER `test`.`TriggerIncl_ItemPedido`$$

CREATE TRIGGER `test`.`TriggerIncl_ItemPedido` BEFORE INSERT on `test`.`ItemPedido`

FOR EACH ROW BEGIN

   declare numero integer;

   Set numero = (select max(ID_Pedido) From Item_Pedido where ID_Pedido = new.ID_Pedido);

   if (numero <= 0) or (numero is null)then

      set numero = 1;

   else

      set numero = numero + 1;

   end if;

   set new.IT_Pedido = numero;

END$$

DELIMITER ;$$

Nota: A crítica (número <= 0) foi colocada para compatibilizar nosso auto_increment com a cláusula auto_increment do MySQL que tem um gotcha na inicialização do próximo registro com um valor positivo imediatamente superior e maior que zero. (Você pode suprimir esta crítica se quizer trabalhar com números negativos)

Agora, para testarmos nossa implementação, vamos cadastrar alguns ítens, suprimindo os valores para o atributo IT_Pedido no insert.

Veja abaixo o exemplo com o cadastro de 6 registros:

Insert into Item_Pedido (ID_Pedido, ID_Produto) Values (1, 34);

Insert into Item_Pedido (ID_Pedido, ID_Produto) Values (1, 45);

Insert into Item_Pedido (ID_Pedido, ID_Produto) Values (1,  1);

Insert into Item_Pedido (ID_Pedido, ID_Produto) Values (2,  9);

Insert into Item_Pedido (ID_Pedido, ID_Produto) Values (2,  1);

Insert into Item_Pedido (ID_Pedido, ID_Produto) Values (2, 22);

Vejamos como ficou o conteúdo da tabela:

Select `ID_Pedido`, `IT_Pedido`, `ID_Produto` From `test`.`ItemPedido`;

Como resposta, teremos:

+---------+---------+----------+

|ID_Pedido|IT_Pedido|ID_Produto|

+---------+---------+----------+

|        1|        1|        34|

|        1|        2|        45|

|        1|        3|         1|

|        2|        1|         9|

|        2|        2|         1|

|        2|        3|        22|

+---------+---------+----------+

Conclusão

Vimos neste artigo, como foi fácil criar uma rotina simples e útil para um problema aparentemente complicado.