SQL FOREIGN KEY

Na Loja Aurora, cada pedido diz de quem ele é com um número: cliente_id = 3 quer dizer "este pedido é da cliente 3". E se alguém gravar um pedido com cliente_id = 999, de um cliente que não existe? O pedido ficaria "órfão": ninguém para entregar, ninguém para cobrar. A chave estrangeira (foreign key) é a regra que impede isso: ela obriga o número a apontar para uma linha que existe de verdade em outra tabela. Nesta aula você vai ver essa regra funcionando, a pegadinha do SQLite (ela vem desligada no terminal) e o que fazer com os filhos quando o "pai" é apagado.

Antes, vale ter visto PRIMARY KEY e Tabelas que se Relacionam. Todos os exemplos rodam de verdade no seu navegador, e cada Rodar começa do banco limpo: pode apagar à vontade.

A ideia: a comanda e o fichário

Pense numa lojinha de bairro com um fichário de clientes, cada ficha com um número. Quando alguém faz uma encomenda, o vendedor anota numa comanda: "cliente nº 3, dois cadernos". A comanda não repete o nome nem o endereço: ela só aponta para a ficha 3.

Agora imagine que a dona da loja tem uma regra: "não se aceita comanda com número de ficha que não está no fichário". E outra: "não se joga fora a ficha de um cliente que ainda tem comanda". É exatamente isso que a chave estrangeira faz no banco:

"Estrangeira" porque é uma chave que vem de fora: cliente_id mora na tabela pedidos, mas o valor dele é a chave de outra tabela.

1. As chaves estrangeiras da Loja Aurora

O banco da loja já foi criado com chaves estrangeiras. Peça ao SQLite o CREATE TABLE da tabela produtos; ele fica guardado na tabela interna sqlite_schema:

SELECT sql
FROM sqlite_schema
WHERE name = 'produtos';

Repare nas duas linhas com REFERENCES:

categoria_id

A coluna da tabela filha (produtos) que guarda o número da categoria.

INTEGER NOT NULL

O tipo e a regra de não ficar vazio (aula NOT NULL).

REFERENCES

"Faz referência a": aqui começa a chave estrangeira.

categorias(id)

A tabela mãe e, entre parênteses, a coluna dela para onde o número aponta. Essa coluna tem de ser a chave primária (ou uma coluna UNIQUE) da mãe.

O SQLite também mostra a lista de chaves estrangeiras de uma tabela, já desmontada, com PRAGMA foreign_key_list:

PRAGMA foreign_key_list(produtos);

As colunas que importam: table (a mãe), from (a coluna desta tabela), to (a coluna da mãe) e on_delete/on_update (o que fazer se a mãe mudar; NO ACTION é o padrão, e você vai entender na seção 5).

2. A regra funcionando

Inserir um filho sem mãe

A Loja Aurora tem 8 categorias, numeradas de 1 a 8. A Ana tenta cadastrar uma caneta na categoria 99:

INSERT INTO produtos (nome, categoria_id, fornecedor_id, preco)
VALUES ('Caneta Gel Azul', 99, 1, 4.5);

A resposta do SQLite: FOREIGN KEY constraint failed, "a regra de chave estrangeira falhou". O produto não foi gravado. Repare que a mensagem não diz qual coluna deu problema: com duas chaves estrangeiras (categoria_id e fornecedor_id), você tem de conferir as duas. Com a categoria 1 (Papelaria), que existe, o mesmo INSERT passa:

INSERT INTO produtos (nome, categoria_id, fornecedor_id, preco)
VALUES ('Caneta Gel Azul', 1, 1, 4.5);

SELECT id, nome, categoria_id
FROM produtos
WHERE id = 46;

INSERT INTO produtos (nome, categoria_id, fornecedor_id, preco)
VALUES ('Regador 5 litros', 80, 1, 29.9);

INSERT INTO produtos (nome, categoria_id, fornecedor_id, preco)
VALUES ('Regador 5 litros', 8, 1, 29.9);

Mudar o número para um que não existe

A regra vale também para o UPDATE. Trocar a categoria do produto 1 para 50 deixaria o produto apontando para o vazio:

UPDATE produtos
SET categoria_id = 50
WHERE id = 1;

Apagar uma mãe que tem filhos

Agora o outro lado da regra. A categoria 1, Papelaria, tem vários produtos. Se a Ana apagar a categoria, esses produtos ficariam apontando para nada:

DELETE FROM categorias
WHERE id = 1;

O mesmo erro. Mas apagar um cliente que nunca fez pedido funciona, porque ninguém aponta para ele. O cliente 27, Paulo Sérgio Andrade, é um dos quatro que nunca compraram:

DELETE FROM clientes
WHERE id = 27;

SELECT COUNT(*) AS clientes FROM clientes;

Já o cliente 1, que tem pedidos, não sai: DELETE FROM clientes WHERE id = 1; dá FOREIGN KEY constraint failed. Para apagá-lo, você teria de apagar (ou passar para outro cliente) os pedidos dele primeiro, e antes ainda os itens desses pedidos, que apontam para os pedidos. Ou deixar o banco fazer isso sozinho, com ON DELETE CASCADE (seção 5).

3. A pegadinha do SQLite: PRAGMA foreign_keys

Aqui o SQLite é diferente de quase todos os outros bancos. A documentação diz: "as chaves estrangeiras vêm desligadas por padrão (por compatibilidade com versões antigas), e precisam ser ligadas separadamente em cada conexão com o banco". Quem liga e desliga é o comando PRAGMA foreign_keys. Um PRAGMA é um comando especial do SQLite para ver e mudar configurações (aula PRAGMAs Úteis).

No editor do guia: ligado

Pergunte ao editor como está a regra:

PRAGMA foreign_keys;

O 1 quer dizer ligado. Isso acontece porque o arquivo que monta a Loja Aurora a cada Rodar começa com PRAGMA foreign_keys = ON;. Foi por isso que todos os erros acima apareceram.

No terminal: desligado

O banco da Ana no computador dela, ~/loja.db, tem exatamente as mesmas tabelas e as mesmas chaves estrangeiras. Mas o ligado/desligado não fica gravado no arquivo: cada vez que o programa sqlite3 abre o banco, a regra começa desligada. Veja o que acontece com a caneta da categoria 99:

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> PRAGMA foreign_keys;
0
sqlite> INSERT INTO produtos (nome, categoria_id, fornecedor_id, preco) VALUES ('Caneta Fantasma', 99, 1, 2.5);
sqlite> SELECT id, nome, categoria_id FROM produtos WHERE id = 46;
46|Caneta Fantasma|99
sqlite> .quit

O 0 quer dizer desligado, e o INSERT passou calado. Agora o banco tem um produto órfão, de uma categoria que não existe. Com a regra ligada logo no começo da sessão (a Ana já tinha apagado a caneta fantasma):

$ sqlite3 loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> INSERT INTO produtos (nome, categoria_id, fornecedor_id, preco) VALUES ('Caneta Fantasma', 99, 1, 2.5);
Runtime error: FOREIGN KEY constraint failed (19)
sqlite> .quit
$ sqlite3 loja.db 'PRAGMA foreign_keys;'
0                        « abriu de novo: voltou a ficar desligado

PRAGMA foreign_keys = ON;

Liga a regra nesta conexão, até fechar o programa. = OFF desliga.

Runtime error:

"Erro na execução": é como o terminal do sqlite3 mostra um erro que aconteceu ao rodar (e não ao ler) o comando.

(19)

O código numérico do erro. 19 é o código de "regra violada" (constraint), o mesmo do UNIQUE e do NOT NULL. O editor do guia mostra só a frase.

Achar os órfãos: PRAGMA foreign_key_check

E se o banco já foi usado com a regra desligada? O PRAGMA foreign_key_check ("conferir chaves estrangeiras") procura as linhas que apontam para o vazio, mesmo com a regra desligada. No terminal, logo depois de gravar a caneta fantasma, a Ana veria:

sqlite> PRAGMA foreign_key_check;
produtos|46|categorias|1

Dá para ver o mesmo no editor: desligue a regra, faça o estrago e confira.

PRAGMA foreign_keys = OFF;

DELETE FROM categorias WHERE id = 1;

PRAGMA foreign_key_check;

table

A tabela filha que tem o problema: produtos.

rowid

O número da linha órfã: os produtos 1 a 6 eram todos da Papelaria.

parent

A tabela mãe que deveria ter a linha: categorias.

fkid

Qual das chaves estrangeiras da tabela falhou, pelo número que aparece no id do PRAGMA foreign_key_list. Aqui, 1 = categoria_id.

Resultado vazio quer dizer "nenhum órfão". No banco limpo da Loja Aurora, o PRAGMA foreign_key_check não devolve nenhuma linha.

4. Criando a sua chave estrangeira

A Ana quer guardar as avaliações que os clientes dão aos produtos. Cada avaliação aponta para um produto e para um cliente. Há duas formas de escrever.

Forma 1: REFERENCES na própria coluna

CREATE TABLE avaliacoes (
    id         INTEGER PRIMARY KEY,
    produto_id INTEGER NOT NULL REFERENCES produtos(id),
    cliente_id INTEGER NOT NULL REFERENCES clientes(id),
    nota       INTEGER NOT NULL
);

INSERT INTO avaliacoes (produto_id, cliente_id, nota)
VALUES (1, 3, 5), (2, 8, 4);

SELECT * FROM avaliacoes;

Forma 2: FOREIGN KEY no fim da tabela

CREATE TABLE avaliacoes (
    id         INTEGER PRIMARY KEY,
    produto_id INTEGER NOT NULL,
    cliente_id INTEGER NOT NULL,
    nota       INTEGER NOT NULL,
    FOREIGN KEY (produto_id) REFERENCES produtos(id),
    FOREIGN KEY (cliente_id) REFERENCES clientes(id)
);

INSERT INTO avaliacoes (produto_id, cliente_id, nota)
VALUES (1, 99, 5);

FOREIGN KEY (cliente_id)

"Chave estrangeira": qual coluna desta tabela é a chave. Fica depois de todas as colunas, separada por vírgula.

REFERENCES clientes(id)

Para onde ela aponta, igual à forma 1.

As duas formas fazem a mesma coisa; o exemplo de cima falhou de propósito, porque não existe o cliente 99. A forma 2 tem duas vantagens: deixa todas as ligações juntas no fim, fáceis de achar, e é a única que serve para chaves de mais de uma coluna (por exemplo, FOREIGN KEY (pedido_id, produto_id) REFERENCES …). Também é a forma que aparece na documentação de todos os bancos grandes.

5. Quando a mãe é apagada: ON DELETE

Até aqui, apagar uma mãe com filhos dava erro. Esse é o comportamento padrão, chamado NO ACTION ("nenhuma ação"): o banco não faz nada com os filhos e recusa a mudança que os deixaria órfãos. Mas você pode escolher outra coisa, escrevendo ON DELETE ("ao apagar") depois do REFERENCES.

ON DELETE CASCADE: os filhos vão junto

A Ana vai guardar os endereços de entrega dos clientes. Um endereço não tem sentido sem o cliente: se o cliente for apagado, os endereços dele podem ir junto. Isso é CASCADE ("em cascata"):

CREATE TABLE enderecos (
    id         INTEGER PRIMARY KEY,
    cliente_id INTEGER NOT NULL
               REFERENCES clientes(id) ON DELETE CASCADE,
    rua        TEXT NOT NULL
);

INSERT INTO enderecos (cliente_id, rua) VALUES
(27, 'Rua das Flores, 10'),
(27, 'Av. Frei Serafim, 200'),
(28, 'Rua Areolino de Abreu, 55');

DELETE FROM clientes WHERE id = 27;

SELECT * FROM enderecos;

ON DELETE

"Ao apagar" a linha da mãe (o cliente)…

CASCADE

…apague também os filhos (os endereços dele).

O DELETE só falava de clientes, mas os dois endereços do cliente 27 sumiram junto; sobrou só o do cliente 28. É poderoso e perigoso na mesma medida: um DELETE errado numa tabela mãe pode apagar muita coisa em outras tabelas sem você ver. Use CASCADE só onde o filho realmente não vive sem o pai (endereços de um cliente, itens de um pedido), nunca em coisas que precisam ficar guardadas, como os pedidos.

ON DELETE SET NULL: o filho fica, sem pai

Para as avaliações, a Ana prefere outra coisa: se o cliente sair da loja, a avaliação continua valendo para o produto, só que "de autor desconhecido":

CREATE TABLE avaliacoes (
    id         INTEGER PRIMARY KEY,
    produto_id INTEGER NOT NULL REFERENCES produtos(id),
    cliente_id INTEGER
               REFERENCES clientes(id) ON DELETE SET NULL,
    nota       INTEGER NOT NULL
);

INSERT INTO avaliacoes (produto_id, cliente_id, nota)
VALUES (1, 27, 5), (2, 28, 4);

DELETE FROM clientes WHERE id = 27;

SELECT * FROM avaliacoes;

A avaliação 1 ficou com cliente_id NULL. Repare que, para isso funcionar, a coluna não pode ser NOT NULL.

Todas as opções

O mesmo vale para ON UPDATE ("ao mudar"), quando o id da mãe muda. Na Loja Aurora, nenhuma chave tem ação, então UPDATE clientes SET id = 100 WHERE id = 1; também dá FOREIGN KEY constraint failed. Mudar a chave primária de uma linha é raro; é mais um motivo para usar números sem significado como chave.

Quando dá errado

DROP TABLE categorias;

CREATE TABLE recados (
    cliente_nome TEXT REFERENCES clientes(nome),
    texto        TEXT
);
INSERT INTO recados VALUES ('Ana Beatriz Sousa', 'Ligar amanhã');

CREATE TABLE recados (
    cliente_id INTEGER REFERENCES cliente(id),
    texto      TEXT
);
INSERT INTO recados VALUES (3, 'Ligar amanhã');

CREATE TABLE enderecos (
    id         INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NOT NULL,
    rua        VARCHAR(200) NOT NULL,
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
);

Referência rápida

Exercícios

Missão final: endereços que vão junto

CREATE TABLE enderecos (
    id         INTEGER PRIMARY KEY,
    cliente_id INTEGER NOT NULL REFERENCES clientes(id),
    rua        TEXT NOT NULL
);

INSERT INTO enderecos (cliente_id, rua) VALUES
(27, 'Rua das Flores, 10'),
(27, 'Av. Frei Serafim, 200'),
(28, 'Rua Areolino de Abreu, 55');

DELETE FROM clientes WHERE id = 27;

SELECT * FROM enderecos;

CREATE TABLE enderecos (
    id         INTEGER PRIMARY KEY,
    cliente_id INTEGER NOT NULL
               REFERENCES clientes(id) ON DELETE CASCADE,
    rua        TEXT NOT NULL
);

INSERT INTO enderecos (cliente_id, rua) VALUES
(27, 'Rua das Flores, 10'),
(27, 'Av. Frei Serafim, 200'),
(28, 'Rua Areolino de Abreu, 55');

DELETE FROM clientes WHERE id = 27;

SELECT * FROM enderecos;

Resumo

• Uma chave estrangeira é uma coluna da tabela filha que aponta para a chave de uma tabela mãe: pedidos.cliente_id → clientes.id.
• Escreva REFERENCES mae(id) na coluna ou FOREIGN KEY (coluna) REFERENCES mae(id) no fim da tabela.
• Com a regra ligada, inserir um filho sem mãe, mudar o número para um que não existe ou apagar uma mãe com filhos dá FOREIGN KEY constraint failed.
• No SQLite, a regra vem desligada em cada conexão: no terminal, PRAGMA foreign_keys; dá 0. Ligue com PRAGMA foreign_keys = ON; toda vez que abrir o banco. No editor do guia ela já vem ligada.
• PRAGMA foreign_key_check; acha as linhas órfãs que entraram com a regra desligada.
• ON DELETE CASCADE apaga os filhos junto; ON DELETE SET NULL os deixa sem pai; o padrão (NO ACTION) recusa.