Relação Muitos-para-Muitos

Um pedido da Loja Aurora pode ter vários produtos. E um produto, a Caneta Esferográfica Azul por exemplo, aparece em vários pedidos. Os dois lados são "muitos". Esse tipo de relação se chama muitos-para-muitos (escrito também N:N) e não cabe numa chave estrangeira comum: precisa de uma terceira tabela no meio, a tabela de ligação. Na aula Tabelas que se Relacionam você viu essa ideia de passagem. Aqui você vai a fundo: por que as soluções "óbvias" falham, como montar a tabela de ligação do zero e como consultar nos dois sentidos.

A ideia: a lista de chamada da escola

Pense numa escola de cursos livres. Cada aluno faz vários cursos, e cada curso tem vários alunos. Onde a secretária anota quem faz o quê? Não na ficha do aluno (não cabem todos os cursos) nem na ficha do curso (não cabem todos os alunos). Ela usa um terceiro papel, a lista de matrículas, com uma linha para cada par:

Ana    — SQL Básico
Ana    — Python
Bruno  — SQL Básico
Carla  — SQL Básico
Carla  — Python
Carla  — Excel

Cada linha diz uma coisa só: "este aluno está neste curso". Para saber os cursos da Carla, ela corre a lista procurando "Carla"; para saber quem faz SQL Básico, procura "SQL Básico". Essa lista é a tabela de ligação, e é assim que um banco guarda uma relação muitos-para-muitos.

1. Reconhecendo um muitos-para-muitos

Faça a pergunta nos dois sentidos. Se as duas respostas forem "vários", é muitos-para-muitos:

No um-para-muitos, a chave estrangeira fica do lado "muitos" (pedidos.cliente_id, aula FOREIGN KEY). No muitos-para-muitos não há um lado "um" para receber a chave — daí a terceira tabela.

2. As soluções "óbvias" e por que falham

Uma coluna para cada item

A primeira ideia costuma ser pôr colunas curso1, curso2, curso3 na tabela de alunos. E quando um aluno fizer quatro cursos? Você teria de mudar a tabela. Para achar quem faz Python, olharia as três colunas, uma a uma. E, para a maioria, sobrariam colunas vazias. Não acompanha o crescimento.

Uma lista em texto

A segunda ideia é guardar os cursos numa coluna de texto, separados por vírgula. Parece prático, até a primeira busca:

CREATE TABLE alunos2 (id INTEGER PRIMARY KEY, nome TEXT, cursos TEXT);
INSERT INTO alunos2 VALUES
  (1, 'Ana',   'SQL Básico, Python'),
  (2, 'Bruno', 'SQL Básico'),
  (3, 'Carla', 'Python, Excel, SQL Básico');

SELECT nome FROM alunos2 WHERE cursos = 'Python';

A busca não achou ninguém, embora Ana e Carla façam Python: o texto inteiro da célula nunca é só 'Python'. Daria para tentar um LIKE '%Python%', mas aí outro problema aparece: LIKE '%SQL%' pegaria tanto "SQL Básico" quanto um eventual "SQL Avançado". Além disso, nada liga o nome do curso a uma tabela de cursos com FOREIGN KEY, nada impede um "Pyton" escrito errado, e contar alunos por curso vira um quebra-cabeça. Guardar vários valores numa célula é uma armadilha; o certo é uma linha por par.

3. A tabela de ligação (o jeito certo)

A solução é uma terceira tabela, com uma linha para cada par e duas chaves estrangeiras, uma para cada lado. Na Loja Aurora, essa tabela já existe: é a itens_pedido, entre pedidos e produtos. Veja os itens do pedido 1:

SELECT * FROM itens_pedido WHERE pedido_id = 1;

pedido_id

Chave estrangeira para pedidos.id: a que pedido o item pertence.

produto_id

Chave estrangeira para produtos.id: qual produto foi comprado.

quantidade, preco_unitario

Dados que não são nem só do pedido nem só do produto, mas do par: quantas unidades daquele produto entraram naquele pedido, e por quanto cada uma. É o lugar natural para eles.

Repare: o par (pedido_id, produto_id) é a chave primária composta da tabela — duas colunas formando a chave (aula PRIMARY KEY). Assim, o mesmo produto não entra duas vezes no mesmo pedido por engano; se for preciso mais, aumenta-se a quantidade.

SELECT * FROM itens_pedido WHERE pedido_id = 1;

SELECT p.nome, i.quantidade
FROM itens_pedido i
JOIN produtos p ON p.id = i.produto_id
WHERE i.pedido_id = 1;

4. Consultando nos dois sentidos

A graça da tabela de ligação é poder ir e voltar. De um pedido para os seus produtos, você acabou de ver. Agora o caminho inverso: em quais pedidos a Caneta Esferográfica Azul (produto 2) foi vendida, e para quem:

SELECT pe.id AS pedido, c.nome AS cliente, i.quantidade
FROM itens_pedido i
JOIN pedidos pe ON pe.id = i.pedido_id
JOIN clientes c ON c.id = pe.cliente_id
WHERE i.produto_id = 2
ORDER BY pe.id;

A caneta saiu em 6 pedidos. Repare que esta consulta passou por três tabelas (itens_pedido → pedidos → clientes): a tabela de ligação é a ponte que permite isso. Um muitos-para-muitos é, no fundo, duas relações um-para-muitos encostadas — um pedido tem muitos itens, e um produto aparece em muitos itens.

Contando os dois lados

Com a tabela de ligação, perguntas como "quantas vezes cada produto foi vendido" saem com um GROUP BY. E, para não esquecer os produtos que nunca venderam, um LEFT JOIN:

SELECT p.nome, COUNT(i.pedido_id) AS vezes
FROM produtos p
LEFT JOIN itens_pedido i ON i.produto_id = p.id
GROUP BY p.id
ORDER BY vezes
LIMIT 5;

Os três primeiros, com vezes igual a 0, são os produtos 43, 44 e 45 (Vaso de Cerâmica Médio, Tesoura de Poda e Luvas de Jardinagem): estão no catálogo, mas nenhuma linha da tabela de ligação aponta para eles. Sem o LEFT JOIN, eles sumiriam da conta.

5. Montando um do zero: alunos × cursos

Agora o exemplo da escola, criado do começo. São três CREATE TABLE: as duas pontas e a ligação. Os dados são inventados (não são do Aurora), só para esta demonstração:

CREATE TABLE alunos (
  id   INTEGER PRIMARY KEY,
  nome TEXT NOT NULL
);

CREATE TABLE cursos (
  id     INTEGER PRIMARY KEY,
  titulo TEXT NOT NULL
);

CREATE TABLE matriculas (
  aluno_id INTEGER NOT NULL REFERENCES alunos(id),
  curso_id INTEGER NOT NULL REFERENCES cursos(id),
  PRIMARY KEY (aluno_id, curso_id)
);

INSERT INTO alunos VALUES (1,'Ana'), (2,'Bruno'), (3,'Carla'), (4,'Davi');
INSERT INTO cursos VALUES (1,'SQL Básico'), (2,'Python'), (3,'Excel'), (4,'Redes');
INSERT INTO matriculas VALUES (1,1), (1,2), (2,1), (3,1), (3,2), (3,3);

SELECT a.nome, c.titulo
FROM alunos a
JOIN matriculas m ON m.aluno_id = a.id
JOIN cursos c ON c.id = m.curso_id
ORDER BY a.nome, c.titulo;

alunos e cursos

As duas pontas, cada uma com a sua chave primária.

matriculas

A tabela de ligação. As duas FKs (aluno_id, curso_id) e a PK composta (aluno_id, curso_id).

o SELECT com dois JOIN

Liga aluno → matrícula → curso, montando a lista de chamada "aluno, curso".

O resultado é a própria lista de matrículas do começo da aula, agora saindo do banco: Ana em dois cursos, Bruno em um, Carla em três. Davi não aparece — ele não tem matrícula (é o nosso "produto nunca vendido" do lado da escola; um LEFT JOIN a partir de alunos o traria).

Quando dá errado

CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
CREATE TABLE cursos (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL);
CREATE TABLE matriculas (
  aluno_id INTEGER NOT NULL REFERENCES alunos(id),
  curso_id INTEGER NOT NULL REFERENCES cursos(id),
  PRIMARY KEY (aluno_id, curso_id)
);
INSERT INTO alunos VALUES (1,'Ana');
INSERT INTO cursos VALUES (1,'SQL Básico');
INSERT INTO matriculas VALUES (1,1);
INSERT INTO matriculas VALUES (1,1);

INSERT INTO itens_pedido VALUES (999, 2, 1, 2.79);

Referência rápida

Exercícios

Missão final: monte a escola

-- 1. CREATE TABLE alunos / cursos
-- 2. CREATE TABLE matriculas (2 FKs, PRIMARY KEY composta)
-- 3. INSERT dos alunos, dos cursos e das 6 matrículas

SELECT 'monte as tabelas acima';

CREATE TABLE alunos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
CREATE TABLE cursos (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL);
CREATE TABLE matriculas (
  aluno_id INTEGER NOT NULL REFERENCES alunos(id),
  curso_id INTEGER NOT NULL REFERENCES cursos(id),
  PRIMARY KEY (aluno_id, curso_id)
);
INSERT INTO alunos VALUES (1,'Ana'), (2,'Bruno'), (3,'Carla');
INSERT INTO cursos VALUES (1,'SQL Básico'), (2,'Python'), (3,'Excel');
INSERT INTO matriculas VALUES (1,1), (1,2), (2,1), (3,1), (3,2), (3,3);

SELECT COUNT(*) FROM matriculas;

Resumo

• Quando os dois lados de uma relação são "muitos" (pedidos × produtos, alunos × cursos), é um muitos-para-muitos.
• Não se resolve com coluna a mais nem com lista em texto: usa-se uma tabela de ligação, com uma linha por par e duas chaves estrangeiras.
• A chave primária da ligação costuma ser o par das duas FKs, o que impede pares repetidos.
• Dados que pertencem ao par (como quantidade) ficam na própria tabela de ligação.
• Consulta-se nos dois sentidos com JOIN; no Aurora, a ligação é a itens_pedido, entre pedidos e produtos.
• O banco protege os dados: UNIQUE barra o par repetido e FOREIGN KEY barra o par que aponta para quem não existe.