Chave estrangeira e o vínculo

Uma chave estrangeira é a coluna que guarda o id de outra tabela. Ela cria o vínculo e, com a integridade referencial ligada, proíbe que o vínculo aponte para linha inexistente.

CREATE TABLE categorias (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  nome TEXT NOT NULL UNIQUE
);

CREATE TABLE tarefas (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  titulo TEXT NOT NULL,
  categoria_id INTEGER,
  FOREIGN KEY (categoria_id) REFERENCES categorias (id)
);

A segunda linha da tabela de tarefas é a que cria o vínculo. Sem FOREIGN KEY, categoria_id é só um número: o aplicativo grava 99 numa categoria que não existe e nada reclama. Com a chave estrangeira e a integridade ligada, o INSERT é recusado.

O nome do recurso é relacionamento, e ele aparece aqui na forma mais simples que existe: duas tabelas e uma coluna que aponta de uma para outra. O que o FOREIGN KEY acrescenta sobre uma coluna comum é a integridade referencial — a regra de que o apontamento tem que valer. Uma coluna INTEGER sozinha permite qualquer número; com a chave estrangeira, o número tem que ser o id de uma linha que existe.

A coluna categoria_id é, por si só, vincular tabelas na forma mais frágil: ela registra o que o aplicativo decidiu gravar. A chave estrangeira transforma isso em regra do banco, e a diferença é quem vai cobrar: sem ela, é o aplicativo; com ela, é o motor.

O UNIQUE no nome da tabela de categorias é uma regra da mesma família, e serve a outro propósito: impede que duas categorias tenham o mesmo nome. É a razão de a coluna nome poder ser usada como identificação na interface, mesmo existindo o id.

A integridade referencial precisa ser ligada

O SQLite vem com a verificação desligada, porque ligado custa alguma velocidade em cada escrita. O comando que liga é executado uma vez por conexão, logo depois de abrir o banco:

await banco.executeSql('PRAGMA foreign_keys = ON');

Sem esse PRAGMA, o FOREIGN KEY está declarado na tabela e não faz nada — a mesma armadilha do IF NOT EXISTS sem efeito. Na prática, o aplicativo que usa chave estrangeira e não liga o PRAGMA passa o semestre inteiro sem ver o vínculo funcionar.

O detalhe da conexão importa: o PRAGMA vale para a conexão, e a tabela relacionada continua existindo com o vínculo declarado enquanto a verificação estiver desligada. Abrir o banco, ligar o PRAGMA e só então rodar as migrações é a ordem correta — a migração que cria a tabela pode, ela mesma, gravar linhas que dependem do vínculo.

O exemplo desta página mostra as duas situações lado a lado: com a verificação desligada, o id 99 de uma categoria que não existe é gravado e o aplicativo recebe confirmação; com o PRAGMA ligado, a mesma gravação é recusada com a mensagem de FOREIGN KEY.

Linha órfã

Com o vínculo ligado, apagar a categoria deixa as tarefas apontando para o nada. É a linha órfã: registro que existe, tem coluna de referência preenchida, e a linha referenciada não existe mais. Há três saídas, e cada uma é uma decisão de produto:

CASCADE é o mais perigoso dos três quando o aplicativo não pensou nele: apagar uma categoria para "arrumar a lista" apaga todas as tarefas junto, e o usuário não foi avisado de nada.

As três regras se escrevem na mesma linha do CREATE TABLE, na chave estrangeira:

FOREIGN KEY (categoria_id) REFERENCES categorias (id) ON DELETE RESTRICT

RESTRICT é a mais segura para um aplicativo que ainda está sendo construído: o banco recusa apagar a categoria e obriga o aplicativo a decidir o que fazer com as tarefas. CASCADE é a mais perigosa pelo mesmo motivo ao contrário — ela resolve o problema do banco transferindo o custo para o usuário. SET NULL é a do meio: preserva a tarefa e perde a classificação, e ela exige que a tela trate null como "sem categoria" em vez de mostrar o nome.

O exemplo da página roda as três regras sobre a mesma cópia do banco e imprime o que cada uma deixou: o RESTRICT devolve o erro dizendo que há tarefas usando a categoria, o CASCADE zera as duas tabelas, e o SET NULL mantém as tarefas com o campo esvaziado.

Exemplo

// Chave estrangeira: a coluna que guarda o `id` de outra tabela. O
// exemplo liga a integridade referencial, mostra a linha orfa e as tres
// saidas do `ON DELETE`.
const banco = {
  categorias: [
    { id: 1, nome: 'estudo' },
    { id: 2, nome: 'trabalho' },
  ],
  tarefas: [
    { id: 1, titulo: 'Revisar o WHERE', categoria_id: 1 },
    { id: 2, titulo: 'Ler o capitulo 4', categoria_id: 1 },
    { id: 3, titulo: 'Enviar o relatorio', categoria_id: 2 },
  ],
};

console.log('CREATE TABLE categorias (id INTEGER PRIMARY KEY AUTOINCREMENT, nome TEXT NOT NULL UNIQUE);');
console.log('CREATE TABLE tarefas (id INTEGER PRIMARY KEY AUTOINCREMENT, titulo TEXT NOT NULL,');
console.log('  categoria_id INTEGER, FOREIGN KEY (categoria_id) REFERENCES categorias (id));');

// `PRAGMA foreign_keys = ON`: sem este comando o vinculo esta declarado
// e nao e verificado
const integridade = { ligada: false };
console.log('integridade referencial ligada?', integridade.ligada);

function inserirTarefa(banco, linha) {
  if (integridade.ligada && linha.categoria_id !== null) {
    const existe = banco.categorias.some((c) => c.id === linha.categoria_id);
    if (!existe) throw new Error('FOREIGN KEY constraint failed: categoria_id');
  }
  banco.tarefas.push(linha);
  return linha.id;
}

// 1. com a integridade desligada, o id 99 entra e ninguem avisa
try {
  const id = inserirTarefa(banco, { id: 4, titulo: 'Tarefa sem categoria', categoria_id: 99 });
  console.log('integridade desligada: gravou o id', id, 'apontando para a categoria 99');
} catch (erro) {
  console.log('recusado:', erro.message);
}

// 2. com a integridade ligada, a mesma gravacao e recusada
integridade.ligada = true;
try {
  inserirTarefa(banco, { id: 5, titulo: 'Outra sem categoria', categoria_id: 99 });
  console.log('gravou (nao deveria ter gravado)');
} catch (erro) {
  console.log('PRAGMA ligado ->', erro.message);
}

// 3. a linha orfa: apagar a categoria deixa a tarefa apontando para o nada
banco.tarefas.pop();
banco.tarefas.pop();
banco.categorias = banco.categorias.filter((c) => c.id !== 2);
const orfas = banco.tarefas.filter((t) => !banco.categorias.some((c) => c.id === t.categoria_id));
console.log('linhas orfas depois de apagar a categoria 2:', orfas.map((t) => t.titulo));

// 4. as tres saidas do ON DELETE
function apagarCategoria(regra) {
  const copia = {
    categorias: banco.categorias.map((c) => ({ ...c })),
    tarefas: banco.tarefas.map((t) => ({ ...t })),
  };
  copia.categorias = copia.categorias.filter((c) => c.id !== 1);
  if (regra === 'RESTRICT' && copia.tarefas.some((t) => t.categoria_id === 1)) {
    return { erro: 'apagar a categoria 1 foi recusado: ha tarefas usando' };
  }
  if (regra === 'CASCADE') copia.tarefas = copia.tarefas.filter((t) => t.categoria_id !== 1);
  if (regra === 'SET NULL') {
    copia.tarefas = copia.tarefas.map((t) => t.categoria_id === 1 ? { ...t, categoria_id: null } : t);
  }
  return { tarefas: copia.tarefas.length, categorias: copia.categorias.length };
}

for (const regra of ['RESTRICT', 'CASCADE', 'SET NULL']) {
  console.log('ON DELETE ' + regra + ' ->', JSON.stringify(apagarCategoria(regra)));
}

Saída real

CREATE TABLE categorias (id INTEGER PRIMARY KEY AUTOINCREMENT, nome TEXT NOT NULL UNIQUE);
CREATE TABLE tarefas (id INTEGER PRIMARY KEY AUTOINCREMENT, titulo TEXT NOT NULL,
  categoria_id INTEGER, FOREIGN KEY (categoria_id) REFERENCES categorias (id));
integridade referencial ligada? false
integridade desligada: gravou o id 4 apontando para a categoria 99
PRAGMA ligado -> FOREIGN KEY constraint failed: categoria_id
linhas orfas depois de apagar a categoria 2: []
ON DELETE RESTRICT -> {"erro":"apagar a categoria 1 foi recusado: ha tarefas usando"}
ON DELETE CASCADE -> {"tarefas":0,"categorias":0}
ON DELETE SET NULL -> {"tarefas":2,"categorias":0}

JOIN para ler o dado junto

Com duas tabelas ligadas, a tela costuma querer as duas coisas na mesma linha: o título da tarefa e o nome da categoria. O JOIN faz essa junção — ele pega as linhas das duas tabelas, casa pelas colunas de vínculo e devolve uma linha combinada.

SELECT tarefas.id, tarefas.titulo, categorias.nome AS categoria
FROM tarefas
JOIN categorias ON tarefas.categoria_id = categorias.id;

O verbo é combinar tabelas, e ele vem antes de tudo: o JOIN não junta duas listas no JavaScript depois, ele faz o casamento dentro do banco e devolve o resultado já combinado. Essa diferença é o que evita ler junto em código: com JOIN, a linha chega na tela com o nome da categoria dentro dela — é o dado de duas tabelas em uma linha só, sem que o aplicativo precise cruzar nada.

O INNER JOIN devolve só as linhas que têm par dos dois lados. LEFT JOIN devolve todas as linhas da tabela da esquerda, e preenche com null o que não encontrou do outro lado. O RIGHT JOIN existe com o mesmo sentido trocado: todas as linhas da direita, e null no que não encontrou da esquerda. Em um aplicativo de tela, RIGHT JOIN quase nunca é o que se quer — ele é útil quando a tabela da esquerda é a acessória e a da direita é a principal.

SELECT tarefas.titulo, categorias.nome AS categoria
FROM tarefas
LEFT JOIN categorias ON tarefas.categoria_id = categorias.id;

A diferença importa no exemplo da linha órfã: com INNER JOIN a tarefa sem categoria simplesmente não aparece na lista; com LEFT JOIN ela aparece, com null no lugar do nome — e é essa a consulta que o aplicativo usa quando precisa mostrar que existe algo sem categoria.

A escolha entre os dois é uma pergunta sobre a lista: "quero ver tudo o que existe" é LEFT JOIN, "quero ver só o que está classificado" é INNER JOIN. O erro clássico do INNER JOIN em tela de lista é a tarefa sem categoria simplesmente não aparecer, e o usuário concluir que ela foi apagada.

ON e AS

ON é a condição do vínculo. Ela precisa apontar a coluna de chave estrangeira de um lado para a chave primária do outro, e o erro mais comum é inverter ou trocar o nome da tabela: ON categorias.id = tarefas.categoria_id funciona igual, e ON tarefas.id = categorias.id não faz sentido nenhum porque não existe coluna cruzada.

O ON é o WHERE do JOIN, e a diferença entre os dois é o que costuma travar a escrita: no WHERE, a condição filtra as linhas já combinadas; no ON, a condição é o próprio casamento. Por isso o WHERE do LEFT JOIN filtra depois, e não muda quem entrou.

AS cria um alias, um nome curto para a coluna que vem da outra tabela — o nome curto da coluna que o mapeamento em JavaScript usa. Sem ele, as duas colunas nome e id chegam com o mesmo nome no resultado, e o mapeamento fica ambíguo. O AS também serve para a tabela: FROM tarefas t JOIN categorias c ON t.categoria_id = c.id encurta a consulta inteira e é o que se escreve quando são três ou mais tabelas.

SELECT t.titulo, c.nome AS categoria
FROM tarefas t
LEFT JOIN categorias c ON t.categoria_id = c.id;

Junção que devolve a contagem

O JOIN com GROUP BY é a pergunta que nenhum filter em JavaScript responde bem: quantas tarefas por categoria, só com as categorias que têm tarefa.

SELECT categorias.nome AS categoria, COUNT(tarefas.id) AS total
FROM categorias
JOIN tarefas ON tarefas.categoria_id = categorias.id
GROUP BY categorias.nome
ORDER BY total DESC;

Cada categoria_id aparece uma vez na tabela de tarefas por tarefa, e o COUNT(tarefas.id) conta uma por linha do grupo. Categoria sem tarefa não entra, porque o INNER JOIN elimina a linha sem par antes do GROUP BY — para ela aparecer com zero, é LEFT JOIN e COUNT(categorias.id).

A diferença entre COUNT(tarefas.id) e COUNT(categorias.id) é o nome curto da coluna que decide o resultado, e é o detalhe que mais gera dúvida: contar a coluna da tabela que pode não ter par dá zero para o grupo vazio, e contar a coluna da tabela principal dá um. Em uma tela que mostra categoria com total, o LEFT JOIN com COUNT(categorias.id) é o que inclui a categoria sem tarefa.

O exemplo desta página implementa a junção em JavaScript para poder contar o que cada tipo devolve, e ele é útil justamente por mostrar o quanto cada JOIN muda o conjunto.

Exemplo

// `JOIN` casa as linhas das duas tabelas pelo vinculo e devolve uma linha
// combinada. `INNER JOIN` so traz o que tem par dos dois lados; `LEFT JOIN`
// traz todas da esquerda e preenche com `null` o que nao encontrou.
const banco = {
  categorias: [
    { id: 1, nome: 'estudo' },
    { id: 2, nome: 'trabalho' },
    { id: 3, nome: 'casa' },
  ],
  tarefas: [
    { id: 1, titulo: 'Revisar o WHERE', categoria_id: 1 },
    { id: 2, titulo: 'Ler o capitulo 4', categoria_id: 1 },
    { id: 3, titulo: 'Enviar o relatorio', categoria_id: 2 },
    { id: 4, titulo: 'Comprar cafe', categoria_id: null },
  ],
};

function juntar(consulta) {
  const esquerda = banco[consulta.de];
  const direita = banco[consulta.para];
  const linhas = [];

  for (const linhaEsquerda of esquerda) {
    // o casamento e a coluna de vinculo da esquerda contra a chave da
    // direita: `tarefas.categoria_id = categorias.id`. Invertido, `linha` e
    // a linha da direita e a comparacao pergunta `categorias.categoria_id`
    // (coluna que nao existe, logo `undefined`) contra `tarefas.id`: nada
    // casa, o `INNER JOIN` devolve zero linhas e o `LEFT JOIN` enche a
    // coluna de nome com `null`.
    const pares = direita.filter((linha) => linhaEsquerda[consulta.chaveEsquerda] === linha[consulta.chaveDireita]);
    if (pares.length === 0) {
      // `INNER JOIN` descarta; `LEFT JOIN` preenche com null
      if (consulta.tipo === 'LEFT') {
        const linha = {};
        for (const nome of Object.keys(linhaEsquerda)) linha[nome] = linhaEsquerda[nome];
        linha[consulta.nomeColunaDireita] = null;
        linhas.push(linha);
      }
      continue;
    }
    for (const linhaDireita of pares) {
      const linha = {};
      for (const nome of Object.keys(linhaEsquerda)) linha[nome] = linhaEsquerda[nome];
      for (const nome of Object.keys(linhaDireita)) linha[nome] = linhaDireita[nome];
      linhas.push(linha);
    }
  }
  return linhas;
}

console.log('SELECT tarefas.titulo, categorias.nome AS categoria');
console.log('FROM tarefas JOIN categorias ON tarefas.categoria_id = categorias.id;');
const inner = juntar({ de: 'tarefas', para: 'categorias', tipo: 'INNER',
  chaveEsquerda: 'categoria_id', chaveDireita: 'id', nomeColunaDireita: 'nome' });
console.log('INNER JOIN devolveu', inner.length, 'linhas:', inner.map((l) => l.titulo + ' / ' + l.nome));

// a tarefa sem categoria aparece com `null` no lugar do nome
const left = juntar({ de: 'tarefas', para: 'categorias', tipo: 'LEFT',
  chaveEsquerda: 'categoria_id', chaveDireita: 'id', nomeColunaDireita: 'nome' });
console.log('LEFT JOIN devolveu', left.length, 'linhas:', left.map((l) => l.titulo + ' / ' + l.nome));

// `LEFT JOIN` do outro lado: a categoria que nao tem tarefa tambem aparece
const categoriasComZero = juntar({ de: 'categorias', para: 'tarefas', tipo: 'LEFT',
  chaveEsquerda: 'id', chaveDireita: 'categoria_id', nomeColunaDireita: 'titulo' });
console.log('categorias pela esquerda:', categoriasComZero.map((c) => c.nome + ' / ' + c.titulo));

// `GROUP BY` com `COUNT`: a pergunta que nenhum `filter` responde bem
function agrupar(linhas, chave) {
  const grupos = new Map();
  for (const linha of linhas) {
    if (!grupos.has(linha[chave])) grupos.set(linha[chave], []);
    grupos.get(linha[chave]).push(linha);
  }
  return [...grupos.entries()].map(([nome, members]) => ({
    chave: nome, total: members.length,
  }));
}

const comTarefa = juntar({ de: 'categorias', para: 'tarefas', tipo: 'INNER',
  chaveEsquerda: 'id', chaveDireita: 'categoria_id', nomeColunaDireita: 'titulo' });
console.log('INNER + GROUP BY + COUNT:', agrupar(comTarefa, 'nome'));

const comZero = juntar({ de: 'categorias', para: 'tarefas', tipo: 'LEFT',
  chaveEsquerda: 'id', chaveDireita: 'categoria_id', nomeColunaDireita: 'titulo' });
console.log('LEFT + GROUP BY + COUNT:', agrupar(comZero, 'nome').map((g) => g.chave + '=' + g.total));

Saída real

SELECT tarefas.titulo, categorias.nome AS categoria
FROM tarefas JOIN categorias ON tarefas.categoria_id = categorias.id;
INNER JOIN devolveu 3 linhas: [
  'Revisar o WHERE / estudo',
  'Ler o capitulo 4 / estudo',
  'Enviar o relatorio / trabalho'
]
LEFT JOIN devolveu 4 linhas: [
  'Revisar o WHERE / estudo',
  'Ler o capitulo 4 / estudo',
  'Enviar o relatorio / trabalho',
  'Comprar cafe / null'
]
categorias pela esquerda: [
  'estudo / Revisar o WHERE',
  'estudo / Ler o capitulo 4',
  'trabalho / Enviar o relatorio',
  'casa / null'
]
INNER + GROUP BY + COUNT: [ { chave: 'estudo', total: 2 }, { chave: 'trabalho', total: 1 } ]
LEFT + GROUP BY + COUNT: [ 'estudo=2', 'trabalho=1', 'casa=1' ]