Uma chave estrangeira NOT VALID do PostgreSQL ignora a verificação inicial das linhas existentes enquanto aplica novas referências. Linhas não correspondentes mais antigas permanecem até serem reparadas e validadas. PostgreSQL 17: ALTER TABLE

Este guia é para um desenvolvedor trabalhando no Terminal em um Mac que deseja adicionar uma chave estrangeira a tabelas populadas e entender os resultados. O exemplo completo com node-postgres usa apenas dados sintéticos de artigos e acompanha uma referência não correspondente desde a criação da restrição até o reparo e a validação bem-sucedida.

O que acontece com as linhas existentes e as novas escritas?

Considere duas tabelas: articles contém IDs e títulos de artigos, e article_links contém referências a esses IDs. Um link está não correspondente quando seu article_id não nulo não tem artigo correspondente. Adicionar a chave estrangeira com NOT VALID permite que essa incompatibilidade mais antiga permaneça enquanto estabelece a restrição para escritas subsequentes. PostgreSQL 17: ALTER TABLE

Operação após adicionar a restrição Resultado neste exemplo
Deixar um link não correspondente mais antigo intocado Ele permanece armazenado
Inserir um link referenciando um artigo existente Ele tem sucesso
Inserir um link referenciando um artigo inexistente Ele falha
Alterar o ID do artigo de um link para outro ID inexistente Ele falha
Validar enquanto um link não correspondente permanece A validação falha
Reparar todas as referências não correspondentes, depois validar A validação pode ter sucesso

Esses resultados seguem a separação documentada entre adicionar e validar a restrição. PostgreSQL 17: ALTER TABLE

Uma exceção importante diz respeito a atualizações que deixam o valor da chave estrangeira inalterado. No PostgreSQL 18, a implementação pode pular a verificação da chave estrangeira quando as chaves antiga e nova são iguais. Consequentemente, alterar apenas o rótulo de um link não correspondente mais antigo pode ter sucesso sem reparar sua referência. A implementação também contém um caso especial para uma linha inserida pela transação atual, portanto, isso não deve ser generalizado para toda sequência de atualizações. Implementação de chave estrangeira do PostgreSQL 18

A consequência prática é que edições de rótulo bem-sucedidas são evidências fracas de limpeza concluída. Inspecione referências não correspondentes diretamente e use a validação para estabelecer que a restrição vale para toda a tabela.

Qual versão do PostgreSQL e formato de tabela isso requer?

O exemplo abaixo usa tabelas comuns, não particionadas, e visa o PostgreSQL 17 e 18. Tabelas particionadas têm um limite de versão significativo: o PostgreSQL 17 proíbe explicitamente declarar chaves estrangeiras em tabelas particionadas como NOT VALID; o PostgreSQL 18 introduziu essa capacidade. Verifique a versão do servidor em vez de inferir suporte a partir do pacote Node instalado. PostgreSQL 17: ALTER TABLE, Notas de lançamento do PostgreSQL 18

Para uma tabela referenciadora particionada no PostgreSQL 17, o caminho direto suportado é corrigir as referências e adicionar a chave estrangeira normalmente, permitindo a varredura de validação inicial. Se a validação em etapas no pai particionado for essencial, o PostgreSQL 18 fornece a capacidade documentada. Este tutorial mantém o layout da tabela simples para que detalhes da hierarquia de partições não obscureçam o comportamento das linhas.

A chave referenciada também precisa de uma garantia de unicidade adequada. Aqui, articles.article_id é uma chave primária. A coluna referenciadora deliberadamente permite NULL: sob a regra padrão MATCH SIMPLE, uma referência nula não precisa de um artigo correspondente. Adicione NOT NULL separadamente se cada link deve identificar um artigo. PostgreSQL 18: CREATE TABLE

Execute um exemplo sintético completo a partir do seu Mac

Você precisa de um banco de dados PostgreSQL em execução, uma configuração de conexão local funcional existente e uma versão do Node.js suportada pelo node-postgres. Em um diretório de projeto no Terminal, instale o pacote com o comando abaixo e salve o programa completo como not-valid-demo.mjs. A introdução oficial do node-postgres documenta a instalação do pacote. documentação do node-postgres

npm install pg

O programa usa new pg.Client(), que lê variáveis de ambiente de conexão do PostgreSQL, como PGHOST, PGPORT, PGDATABASE e PGUSER. Use as configurações que já conectam ao seu banco de dados local. node-postgres: Conectando

Ambas as tabelas são temporárias e existem apenas para a sessão desta conexão. O PostgreSQL exige que chaves estrangeiras de tabelas temporárias referenciem tabelas temporárias, então o programa cria ambos os lados juntos. PostgreSQL 18: CREATE TABLE

Cada operação usa uma consulta aguardada separada. Os valores são passados por parâmetros do node-postgres, como $1 e $2; os nomes da tabela e da restrição são fixos no SQL. node-postgres: Consultas

import pg from 'pg';

const client = new pg.Client({ connectionTimeoutMillis: 5000 });
const validate = `ALTER TABLE pg_temp.article_links
  VALIDATE CONSTRAINT article_links_article_fk`;

async function expectForeignKeyError(label, sql, values = []) {
  try {
    await client.query(sql, values);
  } catch (error) {
    if (error.code !== '23503' ||
        error.constraint !== 'article_links_article_fk') throw error;
    console.log(label + ': 23503 (expected)');
    return;
  }
  throw new Error(label + ': unexpectedly succeeded');
}

async function showStatus(label) {
  const result = await client.query(`
    SELECT conname, convalidated
    FROM pg_constraint
    WHERE conrelid = 'pg_temp.article_links'::regclass
      AND conname = 'article_links_article_fk'
      AND contype = 'f'
  `);
  console.log(label, result.rows);
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows);
  await client.query(`CREATE TEMP TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
  )`);
  await client.query(`CREATE TEMP TABLE article_links (
    link_id integer PRIMARY KEY,
    article_id integer,
    label text NOT NULL
  )`);
  await client.query(
    'INSERT INTO pg_temp.articles VALUES ($1, $2)',
    [1, 'Desk arrangement']
  );
  await client.query(`INSERT INTO pg_temp.article_links VALUES
    (10, 1, 'Matching article'),
    (11, 99, 'Older unmatched reference')`);
  await client.query(`ALTER TABLE pg_temp.article_links
    ADD CONSTRAINT article_links_article_fk
    FOREIGN KEY (article_id)
    REFERENCES pg_temp.articles (article_id) NOT VALID`);
  await showStatus('After adding');

  await expectForeignKeyError(
    'New unmatched reference',
    'INSERT INTO pg_temp.article_links VALUES ($1, $2, $3)',
    [12, 99, 'New reference']
  );
  await expectForeignKeyError(
    'Changed unmatched reference',
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [98, 11]
  );
  await client.query(
    'UPDATE pg_temp.article_links SET label = $1 WHERE link_id = $2',
    ['Revised label', 11]
  );
  console.log('Label-only update succeeded');

  const unmatched = await client.query(`
    SELECT l.link_id, l.article_id, l.label
    FROM pg_temp.article_links AS l
    WHERE l.article_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1 FROM pg_temp.articles AS a
        WHERE a.article_id = l.article_id
      )
    ORDER BY l.link_id
  `);
  console.log('Unmatched references', unmatched.rows);
  await expectForeignKeyError('Validation before repair', validate);
  await showStatus('After failed validation');

  await client.query(
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [1, 11]
  );
  await client.query(validate);
  await showStatus('After successful validation');
} finally {
  await client.end();
}

Execute o programa salvo a partir do mesmo diretório do projeto:

node not-valid-demo.mjs

O JavaScript passou na verificação de sintaxe do Node durante a preparação. A execução do banco de dados não estava disponível no ambiente de pesquisa; os resultados a seguir são esperados a partir do comportamento documentado, e não de um log de execução capturado.

Como você deve interpretar os resultados?

Primeiro, a restrição deve aparecer com convalidated definido como false. O catálogo define esse campo como se a restrição foi validada. A consulta filtra por nome da tabela e da restrição porque nomes de restrições não são necessariamente únicos no banco de dados. PostgreSQL: pg_constraint

Em seguida, a nova inserção não correspondente e a referência não correspondente alterada devem cada uma produzir 23503. O PostgreSQL identifica esse SQLSTATE como foreign_key_violation e recomenda verificar códigos de erro em vez de comparar texto de mensagem localizado. O auxiliar aceita apenas o código e a restrição esperados; falhas inesperadas param o programa. Códigos de erro do PostgreSQL

A atualização apenas de rótulo deixa o link 11 apontando para o artigo 99. A consulta de diagnóstico deve, portanto, retornar exatamente esse link, agora rotulado Revised label. Sua condição explícita IS NOT NULL exclui referências nulas opcionais, enquanto NOT EXISTS identifica IDs não nulos ausentes de articles. Estes são resultados ilustrativos derivados das linhas sintéticas fornecidas.

A validação deve falhar neste ponto. Após o reparo alterar o link 11 para referenciar o artigo 1, a validação deve ter sucesso e convalidated deve se tornar true. VALIDATE CONSTRAINT verifica a restrição anteriormente não validada escaneando a tabela; ele não realiza reparo algum. PostgreSQL: ALTER TABLE

O reparo é uma escolha editorial explícita para esta demonstração: ambos os links devem referenciar Desk arrangement. Para seus próprios dados de artigos, revise cada referência não correspondente antes de decidir se corrige o ID, fornece o artigo ausente ou remove um link obsoleto. Redirecionar automaticamente todo ID não correspondente para um único artigo ocultaria distinções que o banco de dados não pode resolver.

Como aplicar a sequência a tabelas populadas?

Use três etapas separadas: adicione e faça commit da restrição NOT VALID, inspecione e repare referências não correspondentes, depois valide. Adicionar a chave estrangeira obtém um bloqueio SHARE ROW EXCLUSIVE em ambas as tabelas. A validação obtém SHARE UPDATE EXCLUSIVE na tabela referenciadora e ROW SHARE na tabela referenciada, permitindo atualizações comuns concorrentes durante a varredura de validação. A aquisição de bloqueio ainda pode esperar. PostgreSQL: ALTER TABLE

Manter a adição inicial e a validação posterior em transações separadas preserva o propósito da abordagem em etapas: a transação inicial pode terminar antes que a varredura mais longa comece. Agende a adição como uma operação curta e trate a consulta de diagnóstico como auxílio de limpeza, com a validação bem-sucedida como etapa de conclusão.

A demonstração evita deliberadamente um bloco de transação explícito para que falhas esperadas de instruções possam ser tratadas individualmente. Se você adaptar a sequência para transações do node-postgres, emita BEGIN, COMMIT e ROLLBACK você mesmo e mantenha cada instrução em uma transação no mesmo cliente. A documentação do node-postgres diz explicitamente para não usar pool.query() para instruções de transação. node-postgres: Transações

Use as três observações do exemplo juntas: linhas não correspondentes identificam o trabalho de reparo, novas referências rejeitadas demonstram a aplicação contínua, e convalidated = true registra a validação bem-sucedida. Isso lhe dá um ponto de parada concreto para adicionar o relacionamento a tabelas de artigos populadas.

Fontes