Para impedir que um ID de artigo apareça mais de uma vez em qualquer uma de duas colunas de referência, coloque ambos os papéis em uma tabela de referências com um único ID de artigo exclusivo. Se cada linha permitir exatamente uma coluna preenchida, um índice de expressão exclusivo é uma opção mais simples.

Este tutorial é para um desenvolvedor que trabalha no Terminal do macOS com PostgreSQL e node-postgres. A tarefa é impor um conjunto compartilhado de referências a artigos: depois que um artigo for referenciado em qualquer um dos papéis, outra referência a esse artigo deverá falhar. Todas as linhas de exemplo são sintéticas.

O que significa exclusividade entre qualquer uma das colunas?

Suponha que ambas as colunas façam referência ao mesmo catálogo de artigos. A regra pretendida é que cada ID de artigo não nulo apareça no máximo uma vez em todas as posições de referência, incluindo as duas posições dentro de uma mesma linha. Isso é mais rigoroso do que tornar cada coluna exclusiva separadamente.

Pacote Artigo principal Artigo secundário Resultado segundo a regra pretendida
1 101 102 Aceitar
2 103 101 Rejeitar: 101 já aparece no pacote 1
3 104 104 Rejeitar: o mesmo artigo ocupa as duas posições

Um UNIQUE (primary_article_id, secondary_article_id) composto torna exclusiva a combinação. Restrições exclusivas separadas tornam cada coluna exclusiva de forma independente. Nenhum dos dois projetos rejeita a repetição entre colunas nos pacotes 1 e 2. O PostgreSQL documenta que um índice exclusivo de várias colunas rejeita duplicatas quando todos os valores indexados coincidem. Índices exclusivos

Escolha o projeto conforme o formato de linha permitido antes de escrever a consulta de inserção:

Formato de linha exigido Projeto adequado
Ambas as referências podem estar preenchidas Armazene as referências como linhas; torne article_id exclusivo
Exatamente uma referência deve estar preenchida Imponha exatamente uma com uma verificação de linha; indexe exclusivamente COALESCE
Zero ou uma referência pode estar preenchida Use uma verificação de no máximo uma e o mesmo índice de expressão
As colunas fazem referência a catálogos diferentes Primeiro defina se IDs numéricos iguais representam a mesma coisa; este tutorial pressupõe um catálogo compartilhado

Como impor a regra quando ambas as referências são permitidas?

Represente cada referência como uma linha que contenha um pacote, um papel e um ID de artigo. O papel preserva o significado da coluna original. Uma única restrição exclusiva em article_id abrange então ambos os papéis.

O esquema abaixo combina restrições comuns do PostgreSQL: as chaves estrangeiras exigem linhas correspondentes no catálogo, a chave primária composta permite uma referência por pacote e posição, e a restrição exclusiva nomeada permite cada artigo apenas uma vez. Esses mecanismos de restrição estão documentados em Restrições.

Crie um exemplo reproduzível

Use um banco de dados de exemplo local vazio com PostgreSQL 14–18 e um comando psql disponível. Salve a configuração completa a seguir como schema.sql. Ela usa tabelas comuns, não particionadas, e não requer extensões.

BEGIN;

CREATE TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TABLE article_bundles (
    bundle_id integer PRIMARY KEY,
    label text NOT NULL
);

CREATE TABLE bundle_refs (
    bundle_id integer NOT NULL
        REFERENCES article_bundles (bundle_id),
    slot text NOT NULL
        CHECK (slot IN ('primary', 'secondary')),
    article_id integer NOT NULL
        REFERENCES articles (article_id),
    PRIMARY KEY (bundle_id, slot),
    CONSTRAINT bundle_refs_article_once UNIQUE (article_id)
);

INSERT INTO articles (article_id, title) VALUES
    (101, 'Grid spacing'),
    (102, 'Type scale'),
    (103, 'Page margins'),
    (104, 'Caption layout'),
    (105, 'Colour palette');

INSERT INTO article_bundles (bundle_id, label)
VALUES (1, 'Opening notes');

INSERT INTO bundle_refs (bundle_id, slot, article_id) VALUES
    (1, 'primary', 101),
    (1, 'secondary', 102);

COMMIT;

No Terminal do macOS, defina PGDATABASE como o nome desse banco de dados de exemplo existente e execute o arquivo. O comando pressupõe que as configurações da sua conexão local já estejam funcionando.

export PGDATABASE=article_demo
psql -v ON_ERROR_STOP=1 -f schema.sql

Este projeto permite que um pacote tenha zero, uma ou duas referências. Ele impõe a exclusividade e o limite máximo de dois papéis; não exige que todos os pacotes tenham ambos os papéis. Isso é um requisito separado. Uma referência ausente é representada pela ausência de uma linha.

Mantenha o formato de duas colunas para leitura

Os aplicativos ainda podem recuperar uma linha por pacote com dois campos de referência. Execute esta consulta após a configuração:

SELECT b.bundle_id,
       b.label,
       p.article_id AS primary_article_id,
       s.article_id AS secondary_article_id
FROM article_bundles AS b
LEFT JOIN bundle_refs AS p
  ON p.bundle_id = b.bundle_id AND p.slot = 'primary'
LEFT JOIN bundle_refs AS s
  ON s.bundle_id = b.bundle_id AND s.slot = 'secondary'
ORDER BY b.bundle_id;

O resultado inicial é o pacote 1 com as referências 101 e 102. A chave primária do esquema garante que cada junção encontre no máximo uma referência para sua posição. Use esta consulta como formato de leitura do aplicativo em vez de manter uma segunda cópia dos valores de referência.

Como o node-postgres deve inserir ambas as referências?

Crie o pacote e suas referências em uma única transação. Se houver conflito com qualquer referência, reverta também o pacote. O node-postgres exige que cada instrução de uma transação use o mesmo cliente retirado do conjunto de conexões; chamadas separadas a pool.query() não são adequadas para esta sequência. Transações

Em um diretório de projeto local, instale o pacote documentado pg. Use uma versão LTS atual do Node.js e salve o programa abaixo como demo.mjs. Execute-o após a configuração SQL, com o mesmo ambiente de conexão. O node-postgres documenta tanto a instalação quanto as conexões baseadas em variáveis de ambiente.

npm install pg
node demo.mjs

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool();

async function addBundle(bundleId, label, primaryId, secondaryId) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    await client.query(
      'INSERT INTO article_bundles (bundle_id, label) VALUES ($1, $2)',
      [bundleId, label]
    );

    await client.query(
      `INSERT INTO bundle_refs (bundle_id, slot, article_id)
       VALUES ($1, 'primary', $2), ($1, 'secondary', $3)`,
      [bundleId, primaryId, secondaryId]
    );

    await client.query('COMMIT');
    console.log(`Added bundle ${bundleId}`);
  } catch (error) {
    await client.query('ROLLBACK');

    if (
      error.code === '23505' &&
      error.constraint === 'bundle_refs_article_once'
    ) {
      console.log(
        `Bundle ${bundleId} rejected: an article is already referenced`
      );
      return;
    }

    throw error;
  } finally {
    client.release();
  }
}

try {
  await addBundle(2, 'Layout notes', 103, 104);
  await addBundle(3, 'Colour notes', 105, 101);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

O programa passa valores por meio de parâmetros numerados e de um array de valores separado, seguindo a API Consultas do node-postgres. A saída esperada na primeira execução é:

Added bundle 2
Bundle 3 rejected: an article is already referenced

A segunda transação tenta reutilizar o artigo 101. Sua reversão remove o pacote 3 e deixa o artigo 105 sem referência. Apenas o conflito de exclusividade especificamente nomeado recebe a mensagem de duplicata; os demais erros são propagados. O PostgreSQL identifica uma violação de exclusividade com o SQLSTATE 23505 e fornece os nomes das restrições separadamente do texto da mensagem. Códigos de erro do PostgreSQL

Execute novamente a consulta de leitura anterior para inspecionar o resultado: os pacotes 1 e 2 devem permanecer, com as referências 101–104. Considere isto uma demonstração para executar uma vez após cada nova configuração: ao executar o programa novamente, ele encontrará a chave primária já existente do pacote 2.

Quando um índice exclusivo em COALESCE é suficiente?

Use este projeto mais simples somente quando uma linha contiver no máximo uma referência real. COALESCE retorna seu primeiro argumento não nulo, então uma coluna inicial preenchida oculta a segunda coluna da expressão indexada. O PostgreSQL também exige que os argumentos sejam resolvidos para um tipo comum. Expressões condicionais

Por exemplo, as linhas (101, 102) e (103, 101) produzem valores de expressão 101 e 103. Um índice exclusivo nesses valores de expressão aceita ambas, embora o artigo 101 apareça duas vezes. Este contraexemplo explica por que a expressão precisa de uma restrição sobre o formato da linha.

Para uma regra de exatamente uma referência, execute o exemplo alternativo a seguir após criar o catálogo articles anterior. A tabela separada demonstra um esquema alternativo, em vez de adicionar referências a bundle_refs.

CREATE TABLE single_article_refs (
    reference_id integer PRIMARY KEY,
    primary_article_id integer REFERENCES articles (article_id),
    secondary_article_id integer REFERENCES articles (article_id),
    CONSTRAINT exactly_one_article CHECK (
        (primary_article_id IS NULL)
        <> (secondary_article_id IS NULL)
    )
);

CREATE UNIQUE INDEX single_article_refs_article_once
ON single_article_refs (
    (COALESCE(primary_article_id, secondary_article_id))
);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (1, 101, NULL);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (2, NULL, 101)
ON CONFLICT (
    (COALESCE(primary_article_id, secondary_article_id))
)
DO NOTHING
RETURNING reference_id;

A verificação compara dois resultados booleanos não nulos e aceita somente quando uma coluna é nula e a outra está preenchida. O PostgreSQL oferece suporte a verificações que envolvem várias colunas da mesma linha. Restrições do PostgreSQL 14

O índice de expressão exclusivo coloca o valor preenchido de qualquer uma das colunas em um único domínio de comparação. Índices de expressão estão documentados tanto no PostgreSQL atual quanto no PostgreSQL 14; esta abordagem não requer a sintaxe adicional de tratamento de nulos do PostgreSQL 15.

A segunda inserção não retorna linhas porque o artigo 101 já está referenciado. Uma expressão pode ser um alvo de ON CONFLICT, e RETURNING informa as linhas realmente inseridas ou atualizadas. Considere um resultado vazio como uma inserção ignorada. O nome do índice de expressão não é o nome de uma restrição da tabela para ON CONFLICT ON CONSTRAINT. INSERT

Se ambas as colunas puderem estar vazias, substitua a verificação de exatamente uma pela seguinte condição no nível da linha ao definir a tabela. Mantenha o índice de expressão. Assim, várias linhas com ambas as colunas nulas serão permitidas pelo comportamento padrão de nulos dos índices exclusivos do PostgreSQL. Índices exclusivos

-- Alternative CHECK fragment for the CREATE TABLE definition:
CHECK (
    primary_article_id IS NULL
    OR secondary_article_id IS NULL
)

Como diagnosticar duplicatas antes de alterar uma tabela existente?

Expanda ambas as colunas em um único fluxo com UNION ALL e, em seguida, agrupe por ID de artigo. É essencial manter as duplicatas: um UNION comum poderia eliminar evidências da repetição que você precisa encontrar. Este diagnóstico completo usa dados sintéticos e é executado independentemente dos esquemas acima.

WITH legacy_refs (bundle_id, primary_article_id, secondary_article_id) AS (
    VALUES
        (1, 101, 102),
        (2, 103, 101),
        (3, 104, 104)
),
expanded AS (
    SELECT bundle_id, 'primary' AS slot,
           primary_article_id AS article_id
    FROM legacy_refs
    WHERE primary_article_id IS NOT NULL

    UNION ALL

    SELECT bundle_id, 'secondary' AS slot,
           secondary_article_id AS article_id
    FROM legacy_refs
    WHERE secondary_article_id IS NOT NULL
)
SELECT article_id,
       count(*) AS occurrences,
       string_agg(
           bundle_id::text || ':' || slot,
           ', ' ORDER BY bundle_id, slot
       ) AS locations
FROM expanded
GROUP BY article_id
HAVING count(*) > 1
ORDER BY article_id;

Os IDs duplicados esperados são 101 e 104, cada um com duas ocorrências. Para uma tabela existente, substitua a primeira CTE pelas colunas reais da tabela. Revise todas as ocorrências relatadas antes da migração; remover linhas automaticamente poderia descartar referências intencionais. Mova todas as referências para a representação escolhida e direcione as gravações subsequentes para ela, pois as tabelas alternativas separadas do exemplo não compartilham a imposição da exclusividade.

Por que o banco de dados deve impor a decisão final?

Uma consulta preliminar pode explicar um conflito existente, mas não pode, por si só, estabelecer a garantia de exclusividade. Duas operações simultâneas podem observar uma referência não utilizada antes que qualquer uma delas a insira. A implementação de exclusividade do PostgreSQL verifica conflitos como parte da inserção no índice, aguarda transações não confirmadas conflitantes quando necessário e verifica novamente o resultado. Verificações de exclusividade de índices

Não substitua esse mecanismo por um CHECK que consulte outras linhas. O PostgreSQL declara explicitamente que não oferece suporte a verificações entre linhas como garantia mantida continuamente. Use verificações de linha para o formato permitido das colunas e restrições ou índices exclusivos para referências repetidas. Restrições

O programa JavaScript passou por uma verificação de sintaxe, e o contraexemplo e a tabela-verdade da regra de exatamente uma referência foram verificados de forma determinística. Os exemplos do PostgreSQL foram conferidos com a documentação, mas não foram executados em um banco de dados neste ambiente de pesquisa.

Para duas posições de referência preenchidas, o projeto com tabela de referências permite impor diretamente a regra por meio de um único ID de artigo exclusivo. Para uma escolha exclusiva entre colunas, o projeto verificado com COALESCE mantém o esquema mais simples. Escolha com base no formato de linha permitido e, em seguida, deixe o banco de dados tomar a decisão final sobre exclusividade.

Fontes