Modelagem relacional no PostgreSQL: tabelas e chaves

Modelagem relacional é o trabalho de transformar regras do negócio em tabelas, colunas, chaves e relações coerentes. Nesta lição, você vai desenhar uma livraria e provar o modelo tentando inserir dados que deveriam ser recusados.

Os nomes técnicos centrais são entidade, atributo, chave primária, chave estrangeira e cardinalidade. Em termos simples: identifique as coisas que têm vida própria, descreva seus campos, dê uma identidade a cada uma e declare como elas se relacionam.

Pense na planta do estoque da Livraria Horizonte. Cada área tem uma finalidade, cada item recebe um código e portas conectam áreas que precisam conversar. A tabela é uma área, a chave primária é o código da ficha e a chave estrangeira é a referência que aponta para outra área. O limite da analogia: modelagem é lógica, não posição física. O PostgreSQL pode guardar e acessar páginas de formas diferentes sem mudar as relações do modelo.

Comece pelos fatos, não pela lista de tabelas

Antes de abrir o CREATE TABLE, escreva frases que precisam continuar verdadeiras:

• um livro tem um autor principal e pode pertencer a uma categoria;
• um ISBN identifica no máximo um livro cadastrado;
• um cliente pode fazer muitos pedidos;
• um pedido contém muitos livros e um livro aparece em muitos pedidos;
• quantidade e preço no item pertencem àquela compra;
• preço e estoque não podem ser negativos;
• um item não existe sem pedido nem livro.

As entidades emergem dessas frases: autor, categoria, livro, cliente e pedido. “Item de pedido” nasce como relação entre pedido e livro, mas possui atributos próprios — quantidade e preço unitário —, então merece uma tabela.

Essa etapa evita o erro de começar por uma planilha gigantesca com colunas como cliente_nome, cliente_email, livro_1, livro_2 e livro_3. A planilha parece direta até um pedido ter quatro livros, o cliente corrigir o e-mail ou o mesmo autor aparecer em duzentas linhas.

Identidade não deve depender de um dado que muda

O título não é uma boa chave de livro: pode ser corrigido e duas edições podem compartilhá-lo. E-mail pode ser único no cadastro, mas a pessoa pode trocar de endereço. Use um id gerado como chave primária e mantenha regras naturais com UNIQUE quando fizer sentido.

No PostgreSQL, GENERATED ALWAYS AS IDENTITY liga a coluna a uma sequência gerenciada pelo banco. PRIMARY KEY implica unicidade e ausência de nulo e cria automaticamente um índice B-tree único. O id é técnico; ISBN continua sendo uma regra reconhecível pelo negócio.

Catálogo: autor e categoria deixam de ser texto repetido

Crie primeiro as tabelas referenciadas e depois livros. A ordem importa porque a foreign key precisa encontrar seu destino. Use um database vazio para este laboratório:

CREATE TABLE autores (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nome text NOT NULL UNIQUE
);

CREATE TABLE categorias (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nome text NOT NULL UNIQUE
);

CREATE TABLE livros (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  titulo text NOT NULL,
  isbn char(13) NOT NULL UNIQUE,
  autor_id bigint NOT NULL REFERENCES autores(id),
  categoria_id integer REFERENCES categorias(id),
  preco numeric(10,2) NOT NULL CHECK (preco > 0),
  estoque integer NOT NULL DEFAULT 0 CHECK (estoque >= 0),
  publicado_em date
);

autor_id e categoria_id têm o mesmo tipo das chaves apontadas. Autor é obrigatório; categoria pode ser NULL enquanto a curadoria não classifica o livro. Essa nulabilidade representa uma decisão do domínio, não uma preferência de sintaxe.

Separar autores reduz repetição e contradição: corrigir o nome acontece numa linha. Isso não significa separar qualquer texto curto. Status do pedido, por exemplo, tem poucos valores estáveis e pode ser protegido por CHECK sem ganhar uma tabela só para parecer “normalizado”.

Pedido e livro formam uma relação muitos-para-muitos

Um pedido pertence a um cliente, mas contém vários livros. Como cada livro também aparece em vários pedidos, itens_pedido fica no meio:

CREATE TABLE clientes (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nome text NOT NULL,
  email text NOT NULL UNIQUE
);

CREATE TABLE pedidos (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cliente_id bigint NOT NULL REFERENCES clientes(id),
  status text NOT NULL DEFAULT 'criado'
    CHECK (status IN ('criado', 'pago', 'enviado', 'cancelado')),
  criado_em timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE itens_pedido (
  pedido_id bigint REFERENCES pedidos(id) ON DELETE CASCADE,
  livro_id bigint REFERENCES livros(id),
  quantidade integer NOT NULL CHECK (quantidade > 0),
  preco_unitario numeric(10,2) NOT NULL CHECK (preco_unitario > 0),
  PRIMARY KEY (pedido_id, livro_id)
);

A chave primária composta impede duas linhas com o mesmo pedido e livro. Se o sistema precisa permitir o mesmo livro em linhas separadas — edições de personalização, por exemplo —, o item ganha id próprio e outra regra de unicidade.

preco_unitario fica no item mesmo que livros já tenha preco. O catálogo mostra o preço atual; o pedido precisa preservar quanto foi cobrado naquele momento. Guardar o snapshot evita que um reajuste reescreva o histórico.

Insira primeiro os pais, depois as referências

As linhas abaixo respeitam a dependência entre tabelas:

INSERT INTO autores (nome)
VALUES ('Machado de Assis'), ('Clarice Lispector');

INSERT INTO categorias (nome)
VALUES ('Romance brasileiro'), ('Clássicos');

INSERT INTO livros
  (titulo, isbn, autor_id, categoria_id, preco, estoque, publicado_em)
VALUES
  ('Dom Casmurro', '9788535910663', 1, 2, 39.90, 8, '1899-01-01'),
  ('A Hora da Estrela', '9788532508126', 2, 1, 34.50, 5, '1977-10-26');

INSERT INTO clientes (nome, email)
VALUES ('Ana Lima', 'ana@example.com');

Datas históricas aqui são datas civis, por isso usam date. O instante do pedido usa timestamptz, que representa um momento e pode ser exibido no fuso da loja.

Cadastre pedido e itens. O horário é explícito para o laboratório produzir a mesma saída em qualquer fuso do container:

INSERT INTO pedidos (cliente_id, status, criado_em)
VALUES (1, 'pago', '2026-08-22 14:00:00-03')
RETURNING id, cliente_id, status,
          criado_em AT TIME ZONE 'America/Sao_Paulo' AS horario_local;

INSERT INTO itens_pedido (pedido_id, livro_id, quantidade, preco_unitario)
VALUES (1, 1, 1, 39.90),
       (1, 2, 2, 34.50);

Na aplicação, criar pedido, baixar estoque e inserir itens formam uma unidade. A lição de transações mostra como confirmar essas escritas juntas.

Um relatório confirma a cardinalidade do desenho

O pedido tem dois títulos e três unidades. A junção recupera as relações e o agrupamento volta para uma linha por pedido:

SELECT p.id AS pedido,
       c.nome AS cliente,
       count(*) AS titulos,
       sum(i.quantidade) AS unidades,
       sum(i.quantidade * i.preco_unitario) AS total
FROM pedidos AS p
JOIN clientes AS c ON c.id = p.cliente_id
JOIN itens_pedido AS i ON i.pedido_id = p.id
GROUP BY p.id, c.nome;

Se a multiplicação de linhas ainda surpreende, volte à lição de JOIN. A modelagem define a cardinalidade; o join apenas torna os pares visíveis.

Constraints são testes que toda entrada precisa passar

O catálogo do PostgreSQL permite inspecionar as regras que realmente existem, em vez de confiar apenas no desenho:

SELECT tc.table_name,
       tc.constraint_type,
       tc.constraint_name
FROM information_schema.table_constraints AS tc
WHERE tc.table_schema = current_schema()
  AND tc.table_name IN ('livros', 'itens_pedido')
  AND tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE')
ORDER BY tc.table_name, tc.constraint_type, tc.constraint_name;

O filtro usa current_schema(), então funciona tanto no public quanto num schema de laboratório escolhido no search_path. Retire o último AND quando quiser listar também os CHECK e NOT NULL.

Três erros provam três regras diferentes

Primeiro, o mesmo e-mail tenta identificar dois clientes:

INSERT INTO clientes (nome, email)
VALUES ('Outra Ana', 'ana@example.com');

Depois, um livro aponta para um autor inexistente:

INSERT INTO livros (titulo, isbn, autor_id, preco, estoque)
VALUES ('Livro Fantasma', '9780000000001', 999, 29.90, 2);

Por fim, um preço viola o CHECK:

INSERT INTO livros (titulo, isbn, autor_id, preco, estoque)
VALUES ('Preço Impossível', '9780000000002', 1, -10.00, 2);

Cada mensagem aponta o nome da regra e o valor recusado. A resposta não é remover a barreira; é corrigir a entrada ou revisar uma regra de negócio que foi modelada incorretamente. Constraints protegem qualquer cliente que acesse o banco, inclusive scripts e jobs fora da API.

ON DELETE precisa representar o significado da dependência

itens_pedido usa ON DELETE CASCADE: se um pedido for removido, seus itens não têm significado independente. Teste dentro de uma transação para não apagar o histórico do laboratório:

BEGIN;
DELETE FROM pedidos WHERE id = 1;

SELECT count(*) AS itens_depois_do_delete
FROM itens_pedido
WHERE pedido_id = 1;

ROLLBACK;

SELECT count(*) AS itens_depois_do_rollback
FROM itens_pedido
WHERE pedido_id = 1;

Não use cascade como atalho para “limpar tudo ligado”. Autor com livros, por exemplo, pode pedir bloqueio, arquivamento lógico ou migração para outro autor. A ação depende de saber se o filho perde o significado sem o pai.

Sua missão é desenhar e criar uma tabela enderecos ligada a clientes. Decida se um cliente pode ter vários endereços, dê identidade a cada linha, proteja CEP e apelido segundo regras realistas e escolha conscientemente o que acontece ao excluir o cliente. O teste passa quando um endereço com cliente_id inexistente falha, dois clientes podem usar o mesmo CEP e a consulta reconstrói cliente mais endereços sem duplicar dados pessoais.

Você fechou o percurso que começou no CRUD em SQL: agora os comandos operam um modelo com relações e regras. Use o guia de PostgreSQL como mapa de revisão e retorne a índices e EXPLAIN quando as consultas reais mostrarem quais chaves estrangeiras precisam de um caminho adicional.