3.13 Estudo de caso: Distribuidora
Carga das tabelas de uma distribuidora no PostgreSQL: esquema deduzido dos INSERTs, ordem de carga respeitando as chaves estrangeiras e consulta que encontra valores inconsistentes.
O cenário: uma distribuidora
O esquema que os INSERTs revelam
| Tabela | Colunas (além da PK) | PK | Liga-se a |
|---|---|---|---|
| cargo | nome, salario_base | cod_cargo | — |
| fabricante | nome, cidade, telefone | cod_fabricante | — |
| tipo_produto | nome | cod_tipo_produto | — |
| transportadora | nome | cod_transportadora | — |
| funcionario | nome, cidade, ano_contratacao | cod_funcionario | cod_cargo |
| produto | nome, peso, medida, preco, qtde_estoque | cod_produto | cod_tipo_produto, cod_fabricante |
| nota_fiscal | valor_frete | cod_nota_fiscal | cod_transportadora, cod_funcionario |
| item_nota | quantidade, valor | (cod_nota_fiscal, cod_produto) | cod_nota_fiscal, cod_produto |
Pelos dados, item_nota.valor é o total da linha (quantidade × preço): 2 pipocas de 0,45 aparecem com valor 0,90.
A ordem de carga
-- um INSERT com várias linhas no lugar de vários INSERTs
INSERT INTO tipo_produto (cod_tipo_produto, nome)
VALUES (1, 'Açougue'),
(2, 'Mercearia'),
(3, 'Aperitivos');
-- alternativa: FK adiável, conferida só no COMMIT
ALTER TABLE item_nota
ALTER CONSTRAINT item_nota_cod_nota_fiscal_fkey
DEFERRABLE INITIALLY DEFERRED;O nome da restrição acima é o padrão que o PostgreSQL gera (tabela_coluna_fkey); confira o seu com \d item_nota no psql.
⚠ A página original manda rodar os INSERTs na ordem cargo, fabricante, funcionario, item_nota, nota_fiscal, produto, tipo_produto, transportadora. Com as chaves estrangeiras criadas, o primeiro INSERT de item_nota já falha (a nota 1 e o produto 1 ainda não existem). O certo é a ordem desta tela.
Conferindo os valores
SELECT i.cod_nota_fiscal, p.cod_produto, p.nome,
i.quantidade, i.valor,
i.quantidade * p.preco AS esperado
FROM item_nota i
JOIN produto p ON p.cod_produto = i.cod_produto
WHERE i.valor <> i.quantidade * p.preco;⚠ Três itens do original não batem com o preço do produto: nota 3 / produto 12 com 139,90 (a Serra Tico-Tico custa 149,90; 139,90 é o Kit Oficina, produto 14); nota 7 / produto 21 com 799,90 (a Bateria Eletrônica custa 899,90); nota 8 / produto 11 com 1,79 (o Ferro Seco custa 46,90; 1,79 é o Limpador Veja, produto 37). O certo é corrigir o código do produto ou o valor.
Revisão rápida
Por que o INSERT em item_nota falha se vier antes de nota_fiscal?
A FK exige que a nota referenciada já exista.
Quais tabelas podem ser populadas primeiro?
As que não têm FK: cargo, fabricante, tipo_produto e transportadora.
Qual a PK natural de item_nota?
(cod_nota_fiscal, cod_produto): cada produto aparece uma vez por nota.
Como achar itens com valor diferente de quantidade × preço?
Junte item_nota com produto e filtre WHERE valor <> quantidade * preco.