Importação de dados CSV com chaves estrangeiras no PostgreSQL

Suponha que uma exportação de cliente contenha contatos e um company_ref como old-42. Sua tabela de empresas atribui seu próprio ID gerado. Importar old-42 para o ID numérico da empresa falhará, mas fazer uma conversão de tipo ou substituí-lo por um número arbitrário seria pior.

Defina um namespace de correspondência: tenant, sistema de origem e referência externa da empresa. Armazene ou obtenha um mapeamento revisado desse namespace para o ID de destino da empresa. Um nome de empresa geralmente é um substituto ruim: nomes podem se repetir, mudar ou ter grafias inconsistentes. Um domínio de e-mail também pode ser compartilhado por várias entidades de negócio.

“Criar um registro pai aprovado” é uma operação de negócio. Ela pode exigir campos ausentes do arquivo de contatos e pode acionar seus próprios efeitos colaterais. Não crie empresas provisórias automaticamente só para satisfazer uma restrição, a menos que isso seja uma regra intencional da aplicação.

Esta configuração completa usa tabelas temporárias em uma sessão descartável do PostgreSQL 17. Ela representa dois tenants com a mesma referência externa em um sistema de origem.

CREATE TEMP TABLE import_companies_demo (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id integer NOT NULL,
  source_system text NOT NULL,
  external_ref text NOT NULL,
  UNIQUE (tenant_id, source_system, external_ref),
  UNIQUE (tenant_id, id)
);

INSERT INTO import_companies_demo
  (tenant_id, source_system, external_ref)
VALUES (10, 'legacy-crm', 'old-42'),
       (20, 'legacy-crm', 'old-42');

CREATE TEMP TABLE import_contacts_stage (
  source_record integer PRIMARY KEY,
  email text NOT NULL,
  company_ref text NOT NULL
);

INSERT INTO import_contacts_stage VALUES
  (1, 'first@example.test', 'old-42'),
  (2, 'second@example.test', 'missing-99');

O tenant autorizado da importação é 10 e seu sistema de origem aprovado é legacy-crm. Em uma implementação real, esses são dados de entrada confiáveis da tarefa; neste exemplo, são constantes. Verifique a resolução antes de inserir contatos:

SELECT stage.source_record, stage.company_ref,
       count(company.id) AS matches
FROM import_contacts_stage AS stage
LEFT JOIN import_companies_demo AS company
  ON company.tenant_id = 10
 AND company.source_system = 'legacy-crm'
 AND company.external_ref = stage.company_ref
GROUP BY stage.source_record, stage.company_ref
HAVING count(company.id) <> 1
ORDER BY stage.source_record;

O resultado identifica o registro 2 com zero correspondências. A restrição de unicidade no namespace de empresas impede várias correspondências neste esquema. Se o destino real não tiver essa restrição, o mesmo diagnóstico pode expor uma ambiguidade que precisa ser corrigida antes que uma regra de correspondência única possa ser considerada confiável.

Um inner join sozinho descartaria silenciosamente o registro 2. Sua saída poderia parecer uma importação bem-sucedida de um contato. O diagnóstico explícito faz da exclusão uma decisão, em vez de um efeito colateral acidental do SQL.

Para este ensaio, suponha que o responsável pelos dados aprove a correção do registro 2 para old-42. Essa é uma correção sintética, não uma alternativa geral para referências ausentes. O bloco a seguir é um ensaio que termina em rollback; use o resultado do SELECT para inspecionar os registros resolvidos.

BEGIN;

UPDATE import_contacts_stage
SET company_ref = 'old-42'
WHERE source_record = 2;

CREATE TEMP TABLE import_contacts_target (
  tenant_id integer NOT NULL,
  source_record integer NOT NULL,
  email text NOT NULL,
  company_id bigint NOT NULL,
  PRIMARY KEY (tenant_id, source_record),
  FOREIGN KEY (tenant_id, company_id)
    REFERENCES import_companies_demo (tenant_id, id)
);

INSERT INTO import_contacts_target
  (tenant_id, source_record, email, company_id)
SELECT 10, stage.source_record, stage.email,
       (SELECT company.id
        FROM import_companies_demo AS company
        WHERE company.tenant_id = 10
          AND company.source_system = 'legacy-crm'
          AND company.external_ref = stage.company_ref)
FROM import_contacts_stage AS stage;

SELECT * FROM import_contacts_target ORDER BY source_record;
ROLLBACK;

Os dois registros são resolvidos para a empresa no tenant 10. Uma subconsulta escalar sem correspondência produz NULL, que o destino rejeita por meio de NOT NULL. Várias correspondências fariam a subconsulta escalar falhar. Isso evita a perda silenciosa de linhas de um inner join.

A chave estrangeira composta verifica se o par de tenant e ID de empresa existe em conjunto. Uma chave estrangeira apenas no ID de empresa estabeleceria somente a existência global. Ela não estabeleceria a consistência de tenant. A chave primária aqui usa um número de registro de origem para demonstração; um importador reutilizável precisa de um namespace de importação ou tarefa, ou de uma identidade de negócio estável e aprovada, para distinguir arquivos separados.

A autorização da aplicação e a RLS continuam necessárias onde a aplicação depende delas. A integridade referencial não é um sistema completo de controle de acesso.

Ao importar registros pais e filhos, disponibilize primeiro as identidades dos pais. Insira o conjunto aprovado de pais, capture ou resolva o mapa de IDs resultante e depois converta as referências dos filhos usando esse mapa. Mantenha o namespace no escopo do tenant e vinculado ao sistema de origem.

Não pressuponha que IDs gerados sejam consecutivos, correspondam à ordem das linhas do CSV ou possam ser reconstruídos a partir do primeiro ID retornado mais um deslocamento. Transações concorrentes, tentativas com falha e o comportamento de sequências invalidam essas suposições. Use correspondência explícita entre chave e ID.

Um script pontual pode manter um mapa revisado dentro de uma transação quando o conjunto de dados é pequeno. Uma migração que possa ser retomada precisa de evidências persistentes do mapeamento que sobrevivam à reinicialização do processo e impeçam uma nova tentativa de criar um segundo registro pai. Sua política de retenção e acesso deve refletir os dados de origem que contém.

Se a origem contiver várias representações do mesmo registro pai, decida como deduplicá-las antes de criar os filhos. É nesse ponto que uma migração pode crescer além de uma importação de tabela única. O guia de identidade e valores padrão trata separadamente das regras de fornecimento de IDs.

Uma chave estrangeira anulável pode significar legitimamente “nenhuma empresa atribuída”. Ela não deve significar silenciosamente “a empresa fornecida não foi encontrada”. Mantenha esses resultados separados na revisão da origem e no resultado.

Uma segunda etapa pode preencher uma referência depois que as duas linhas existirem, mas somente quando o destino permitir o NULL temporário e a aplicação puder tolerá-lo. Se cada etapa for confirmada separadamente, o estado intermediário fica visível e pode acionar fluxos de trabalho. Se ambas estiverem em uma única transação, o estado do banco se torna visível em conjunto no commit, mas você ainda precisa entender os triggers e efeitos externos.

Uma chave estrangeira adiável pode postergar sua verificação para um momento posterior na mesma transação. Ela não cria uma linha referenciada ausente, não torna a coluna anulável nem permite que uma violação permaneça no commit. Somente restrições declaradas como DEFERRABLE podem ter seu momento de verificação adiado; restrições NOT NULL e CHECK não são adiadas dessa forma no PostgreSQL 17. Veja SET CONSTRAINTS.

Relacionamentos cíclicos precisam de um plano específico de esquema e transação. As opções podem incluir estados anuláveis válidos, referências adiadas ou uma reformulação no nível do domínio. Desativar chaves estrangeiras não é um procedimento geral de recuperação. Teste a estratégia selecionada contra os invariantes reais da aplicação.

Verifique se todo registro de origem aceito foi contabilizado e se sua referência resolvida corresponde ao mapeamento revisado. Conte registros ausentes separadamente dos registros vinculados ao pai errado. Um banco pode impor uma referência válida enquanto o importador escolhe a empresa válida errada.

Consultas de pré-validação podem ficar desatualizadas. Mantenha as restrições finais e execute o mapeamento e a gravação com uma estratégia de transação adequada. Decida como tratar a exclusão de registros pais, a reatribuição de referências ou uma importação concorrente. Para uma migração pontual controlada, coordenar a janela de gravação pode ser mais simples do que construir um serviço de migração concorrente.

Use as verificações pós-importação para comparar as identidades aceitas e os campos importantes e depois teste as visualizações habituais de relacionamentos da aplicação.