Guia de relacionamentos em bancos de dados

Um relacionamento em banco de dados liga linhas de uma tabela a linhas de outra por meio de uma chave estrangeira: uma coluna que guarda a chave primária da linha relacionada. Existem três tipos de relacionamento, um-para-um, um-para-muitos e muitos-para-muitos, e todos são construídos com chaves estrangeiras. Os exemplos funcionam em PostgreSQL, MySQL, SQLite e SQL Server.

Como criar relacionamentos em bancos de dados

Dê à tabela filha uma coluna para o id da tabela pai e declare-a como chave estrangeira, para que o banco recuse valores que não correspondam a uma linha pai real:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Faça o join pela chave estrangeira para juntar as linhas relacionadas. A constraint também impede linhas que não apontariam para nada:

-- Input: customers
-- id | name
-- ---+-----------
-- 1  | Ana Lima
-- 2  | Ben Okafor

-- Input: orders
-- id | customer_id | total
-- ---+-------------+------
-- 10 | 1           | 25.00
-- 11 | 2           | 15.50
-- 12 | 1           | 40.00

SELECT c.name, o.id AS order_id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

-- Output:
-- name       | order_id | total
-- -----------+----------+------
-- Ana Lima   | 10       | 25.00
-- Ben Okafor | 11       | 15.50
-- Ana Lima   | 12       | 40.00

INSERT INTO orders (id, customer_id, total) VALUES (13, 99, 10.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- "orders_customer_id_fkey"

DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- "orders_customer_id_fkey" on table "orders"

Regras importantes

• O SQLite ignora chaves estrangeiras, a menos que você as ative. Execute PRAGMA foreign_keys = ON; em toda conexão, ou o INSERT inválido acima será aceito.
• Escreva a chave estrangeira em uma linha própria FOREIGN KEY (...) REFERENCES ..., como acima. O MySQL 8.0 ignora silenciosamente um REFERENCES escrito na própria coluna (customer_id INTEGER REFERENCES customers(id)), e linhas inválidas entram direto.
• Decida o que acontece ao excluir uma linha pai. Por padrão, o banco recusa, como acima. Adicione ON DELETE CASCADE à chave estrangeira para excluir também as linhas filhas, ou ON DELETE SET NULL para mantê-las sem pai.
• Crie um índice na coluna da chave estrangeira. Você vai fazer joins e filtros nela o tempo todo, e só o MySQL cria o índice automaticamente: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
• Use o mesmo tipo da chave para a qual ela aponta, como INTEGER para um id INTEGER.
• Nunca guarde uma lista de ids em uma única coluna, como '1,3,5'. O banco não consegue validá-la nem fazer join nela. Isso é um relacionamento muitos-para-muitos e exige uma tabela de junção (veja abaixo).

Sintaxe alternativa

Dê um nome à constraint para que os erros fiquem mais fáceis de ler e seja mais simples removê-la depois:

CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)

Para adicionar uma chave estrangeira a uma tabela existente, use ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id);. O SQLite não permite isso: crie uma nova tabela com a chave estrangeira, copie as linhas e troque as tabelas.

Para ids preenchidos pelo próprio banco, use GENERATED ALWAYS AS IDENTITY (ou SERIAL) no PostgreSQL, AUTO_INCREMENT no MySQL, IDENTITY(1,1) no SQL Server e INTEGER PRIMARY KEY no SQLite.

Quando usar relacionamentos em bancos de dados?

Use um relacionamento sempre que dois tipos de coisa estiverem conectados. O tipo de relacionamento depende de quantas linhas podem existir de cada lado.

Dividir uma tabela em duas: um-para-um

Cada linha corresponde a no máximo uma linha da outra tabela. Adicione uma chave estrangeira UNIQUE à segunda tabela. Use isso para tirar colunas opcionais ou pouco usadas de uma tabela larga, ou para manter dados sensíveis (usuários e seus perfis, funcionários e seus registros salariais) com permissões separadas.

Dar vários filhos a uma linha: um-para-muitos

O relacionamento mais comum: um cliente tem muitos pedidos, um autor tem muitos posts. Coloque uma chave estrangeira simples no lado “muitos”. O pai pode até ser a mesma tabela, como quando employees.manager_id aponta para outro funcionário.

Ligar muitas linhas a muitas outras: muitos-para-muitos

Alunos fazem vários cursos e cursos têm vários alunos, então nenhuma das tabelas pode guardar o id da outra. Adicione uma tabela de junção com uma chave estrangeira para cada lado e uma chave primária no par, para que o mesmo vínculo não seja gravado duas vezes. Informações sobre o próprio vínculo, como uma nota, ficam na tabela de junção.

Encontrar linhas sem correspondência do outro lado

Um relacionamento também mostra o que está faltando. Um LEFT JOIN pela chave estrangeira mantém todas as linhas da primeira tabela e preenche com NULL onde não há correspondência; assim, filtrar por NULL encontra clientes sem pedidos ou cursos que ninguém fez.

Exemplos reais de relacionamentos em bancos de dados

Usuários e seus perfis

profiles.user_id é UNIQUE, então um usuário não pode ter um segundo perfil. O LEFT JOIN mantém os usuários que ainda não criaram um:

-- profiles.user_id is declared as: user_id INTEGER UNIQUE NOT NULL

-- Input: users
-- id | username
-- ---+---------
-- 1  | mia
-- 2  | raj
-- 3  | zoe

-- Input: profiles
-- id | user_id | bio
-- ---+---------+---------------------
-- 1  | 1       | Software developer
-- 2  | 2       | Marketing specialist

SELECT u.username, p.bio
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
ORDER BY u.id;

-- Output:
-- username | bio
-- ---------+---------------------
-- mia      | Software developer
-- raj      | Marketing specialist
-- zoe      | NULL

INSERT INTO profiles (id, user_id, bio) VALUES (3, 1, 'Second profile');
-- ERROR: duplicate key value violates unique constraint "profiles_user_id_key"

Funcionários e seus gerentes

Um relacionamento um-para-muitos dentro de uma única tabela: manager_id é uma chave estrangeira para employees.id. Faça o join da tabela com ela mesma para mostrar cada funcionário ao lado do seu gerente:

-- Input: employees
-- id | name      | manager_id
-- ---+-----------+-----------
-- 1  | Maya Chen | NULL
-- 2  | Sam Ortiz | 1
-- 3  | Jo Kim    | 2
-- 4  | Lee Ross  | 2

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;

-- Output:
-- employee  | manager
-- ----------+----------
-- Maya Chen | NULL
-- Sam Ortiz | Maya Chen
-- Jo Kim    | Sam Ortiz
-- Lee Ross  | Sam Ortiz

Alunos e seus cursos

A tabela de junção enrollments tem uma chave estrangeira para cada lado e PRIMARY KEY (student_id, course_id). Faça o join através dela para listar quem faz qual curso:

-- Input: students
-- id | name
-- ---+------
-- 1  | Priya
-- 2  | Tomas
-- 3  | Leah

-- Input: courses
-- id | title
-- ---+----------------
-- 1  | Database Design
-- 2  | Web Development
-- 3  | Statistics

-- Input: enrollments
-- student_id | course_id | grade
-- -----------+-----------+------
-- 1          | 1         | A
-- 1          | 2         | B+
-- 2          | 1         | A-
-- 3          | 2         | A

SELECT s.name AS student, c.title AS course, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY s.name, c.title;

-- Output:
-- student | course          | grade
-- --------+-----------------+------
-- Leah    | Web Development | A
-- Priya   | Database Design | A
-- Priya   | Web Development | B+
-- Tomas   | Database Design | A-

INSERT INTO enrollments VALUES (1, 1, 'B');
-- ERROR: duplicate key value violates unique constraint "enrollments_pkey"

Cursos sem nenhum aluno matriculado

NOT EXISTS é outra forma de encontrar linhas sem correspondência. Ele mantém cada curso que não tem nenhuma linha na tabela de junção:

-- Input: the courses and enrollments tables above

SELECT c.title
FROM courses c
WHERE NOT EXISTS (
  SELECT 1 FROM enrollments e WHERE e.course_id = c.id
);

-- Output:
-- title
-- ----------
-- Statistics

Leitura complementar sobre relacionamentos em bancos de dados

Neste blog:

• Guia de relacionamentos um-para-um: um-para-um em detalhes
• Guia de relacionamentos um-para-muitos: join, contagem e agregação de linhas filhas
• Guia de relacionamentos muitos-para-muitos: tabelas de junção em detalhes
• Guia de SQL JOIN com diagramas: todos os tipos de join que você vai usar em um relacionamento
• Como usar chaves estrangeiras no PostgreSQL: opções de chave estrangeira no PostgreSQL
• ON DELETE CASCADE no PostgreSQL: excluir linhas filhas junto com o pai
• Chaves primárias compostas em SQL: a chave de duas colunas usada por uma tabela de junção
• Guia de SQL CREATE TABLE: chaves e constraints ao criar uma tabela

Documentação oficial:

• PostgreSQL: chaves estrangeiras
• MySQL: constraints FOREIGN KEY
• SQLite: suporte a chaves estrangeiras
• SQL Server: constraints de chave primária e estrangeira