Importar datos CSV con claves foráneas en PostgreSQL

Supongamos que una exportación de un cliente contiene contactos y una company_ref como old-42. Tu tabla de empresas asigna su propio ID generado. Importar old-42 en el ID numérico de empresa fallará, pero forzar su conversión de tipo o sustituirlo por un número arbitrario sería peor.

Define un espacio de nombres para buscar coincidencias: tenant, sistema de origen y referencia externa de empresa. Guarda u obtén un mapeo revisado de ese espacio de nombres al ID de empresa de destino. El nombre de una empresa suele ser un mal sustituto: los nombres pueden repetirse, cambiar o presentar variaciones ortográficas. Varias entidades empresariales también pueden compartir un dominio de correo electrónico.

«Crear un registro padre aprobado» es una operación de negocio. Puede requerir campos que no estén en el archivo de contactos y provocar sus propios efectos secundarios. No crees automáticamente empresas provisionales solo para satisfacer una restricción, salvo que sea una regla intencional de la aplicación.

Esta preparación completa usa tablas temporales en una sesión desechable de PostgreSQL 17. Representa dos tenants con la misma referencia externa en un mismo sistema de origen.

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');

El tenant autorizado de la importación es 10 y su sistema de origen aprobado es legacy-crm. En una implementación real son entradas de confianza del trabajo; en este ejemplo son constantes. Comprueba la resolución antes de insertar contactos:

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;

El resultado identifica el registro 2 con cero coincidencias. La restricción de unicidad sobre el espacio de nombres de empresa evita varias coincidencias en este esquema. Si el destino real no tiene esa restricción, el mismo diagnóstico puede revelar una ambigüedad que debe corregirse antes de confiar en una regla de coincidencia única.

Un inner join por sí solo descartaría silenciosamente el registro 2. Su salida podría parecer una importación correcta de un contacto. El diagnóstico explícito convierte la exclusión en una decisión en lugar de un efecto secundario accidental del SQL.

Para este ensayo, supongamos que el responsable de los datos aprueba corregir el registro 2 a old-42. Es una corrección sintética, no una alternativa general para referencias ausentes. El siguiente bloque es un ensayo que siempre termina en rollback; usa su resultado SELECT para inspeccionar los registros resueltos.

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;

Los dos registros se resuelven a la empresa del tenant 10. Una subconsulta escalar sin coincidencias produce NULL, que el destino rechaza mediante NOT NULL. Varias coincidencias harían fallar la subconsulta escalar. Así se evita la pérdida silenciosa de filas de un inner join.

La clave foránea compuesta verifica que la pareja de tenant e ID de empresa exista conjuntamente. Una clave foránea solo sobre el ID de empresa establecería únicamente la existencia global. No establecería la coherencia del tenant. Aquí, la clave primaria usa un número de registro de origen como demostración; un importador reutilizable necesita un espacio de nombres de importación o trabajo, o una identidad de negocio estable y aprobada, para distinguir archivos diferentes.

La autorización de la aplicación y la RLS siguen siendo necesarias cuando la aplicación depende de ellas. La integridad referencial no es un sistema completo de control de acceso.

Al importar tanto registros padre como hijos, haz que las identidades de los padres estén disponibles primero. Inserta el conjunto de padres aprobado, captura o resuelve el mapeo de IDs resultante y traduce después las referencias de los hijos con ese mapeo. Mantén el espacio de nombres limitado al tenant y vinculado al sistema de origen.

No supongas que los IDs generados son consecutivos, coinciden con el orden de las filas del CSV o pueden reconstruirse a partir del primer ID devuelto más un desplazamiento. Las transacciones concurrentes, los intentos fallidos y el comportamiento de las secuencias invalidan esas suposiciones. Usa una correspondencia explícita de clave a ID.

Un script puntual puede mantener un mapeo revisado dentro de una transacción cuando el conjunto de datos es pequeño. Una migración reanudable necesita pruebas persistentes del mapeo que sobrevivan a un reinicio del proceso e impidan que un reintento cree un segundo registro padre. Su política de conservación y acceso debe reflejar los datos de origen que contiene.

Si el origen contiene varias representaciones del mismo registro padre, decide cómo deduplicarlas antes de crear los hijos. En este punto, una migración puede ir más allá de la importación de una sola tabla. La guía de identidades y valores predeterminados trata por separado las reglas para aportar IDs.

Una clave foránea que admite NULL puede significar legítimamente «sin empresa asignada». No debería significar silenciosamente «no se encontró la empresa indicada». Mantén esos resultados separados en la revisión del origen y en el resultado.

Una segunda pasada puede completar una referencia después de que existan ambas filas, pero solo cuando el destino permita el NULL temporal y la aplicación pueda tolerarlo. Si cada pasada se confirma por separado, el estado intermedio es visible y puede activar flujos de trabajo. Si ambas pasadas están en una sola transacción, el estado de la base de datos se hace visible conjuntamente en el commit, pero aun así debes comprender los disparadores y los efectos externos.

Una clave foránea diferible puede posponer su comprobación hasta más adelante en la misma transacción. No crea una fila referenciada ausente, no hace que la columna admita NULL ni permite que una infracción permanezca al confirmar la transacción. Solo puede diferirse el momento de comprobación de las restricciones declaradas DEFERRABLE; las restricciones NOT NULL y CHECK no se difieren de esta forma en PostgreSQL 17. Consulta SET CONSTRAINTS.

Las relaciones cíclicas necesitan un esquema y un plan de transacciones específicos. Las opciones pueden incluir estados válidos que admitan NULL, referencias diferidas o un rediseño del dominio. Desactivar claves foráneas no es un procedimiento general de recuperación. Prueba la estrategia elegida con los invariantes reales de la aplicación.

Comprueba que se dé cuenta de cada registro de origen aceptado y que su referencia resuelta coincida con el mapeo revisado. Cuenta por separado los registros ausentes y los vinculados a un padre incorrecto. Una base de datos puede imponer una referencia válida mientras el importador elige una empresa válida pero equivocada.

Las consultas previas pueden quedar desactualizadas. Mantén las restricciones finales y ejecuta el mapeo y la escritura con una estrategia de transacciones adecuada. Decide cómo gestionar la eliminación de padres, la reasignación de referencias o una importación concurrente. Para una migración puntual controlada, coordinar la ventana de escritura puede ser más sencillo que crear un servicio de migración concurrente.

Usa las comprobaciones posteriores a la importación para comparar las identidades aceptadas y los campos importantes, y prueba después las vistas habituales de relaciones de la aplicación.