Acervo.estudos
Trilhas Enviar material Entrar
mapa · Intermediário

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.

por @julio · rev. 23 set · 5 telas

Minimundo do estudo de caso

O cenário: uma distribuidora

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
Oito tabelas; as colunas cod_* são as ligações

O esquema que os INSERTs revelam

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
TabelaColunas (além da PK)PKLiga-se a
cargonome, salario_basecod_cargo—
fabricantenome, cidade, telefonecod_fabricante—
tipo_produtonomecod_tipo_produto—
transportadoranomecod_transportadora—
funcionarionome, cidade, ano_contratacaocod_funcionariocod_cargo
produtonome, peso, medida, preco, qtde_estoquecod_produtocod_tipo_produto, cod_fabricante
nota_fiscalvalor_fretecod_nota_fiscalcod_transportadora, cod_funcionario
item_notaquantidade, 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.

Pai antes de filho

A ordem de carga

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
-- 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.

Uma consulta que acha dado inconsistente

Conferindo os valores

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
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.

Teste antes de seguir

Revisão rápida

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
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.

← Voltar ao curso