Importing CSV data with foreign keys in PostgreSQL

Choose the identity used for matching

Suppose a customer export contains contacts and a company_ref such as old-42. Your companies table assigns its own generated ID. Importing old-42 into the numeric company ID will fail, but casting or replacing it with an arbitrary number would be worse.

Define a matching namespace: tenant, source system and external company reference. Store or obtain a reviewed mapping from that namespace to the destination company ID. A company name is usually a poor substitute: names can repeat, change or contain inconsistent spelling. An email domain can also be shared by several business entities.

“Create an approved parent” is a business operation. It may require fields not present in the contacts file, and it may trigger its own side effects. Do not auto-create placeholder companies just to satisfy a constraint unless that is an intentional application rule.

Resolve a tenant-scoped external reference

This complete setup uses temporary tables in a disposable PostgreSQL 17 session. It represents two tenants with the same external reference in one source system.

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

The import’s authorized tenant is 10 and its approved source system is legacy-crm. Those are trusted job inputs in a real implementation; they are constants in this example. Check resolution before inserting contacts:

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;

The result identifies record 2 with zero matches. The unique constraint on the company namespace prevents multiple matches in this schema. If the real target lacks that constraint, the same diagnostic can expose ambiguity that needs correction before a unique matching rule can be trusted.

An inner join alone would silently drop record 2. Its output might look like a successful import of one contact. The explicit diagnostic makes the exclusion a decision instead of an accidental side effect of SQL.

Keep the tenant relationship in the constraint

For this rehearsal, suppose the data owner approves correcting record 2 to old-42. That is a synthetic correction, not a general missing-reference fallback. The following block is a rollback-only rehearsal; use its SELECT result to inspect the resolved records.

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;

The two records resolve to the company in tenant 10. A scalar subquery with no match produces NULL, which the target rejects through NOT NULL. Several matches would cause the scalar subquery to fail. This avoids the silent row loss of an inner join.

The composite foreign key verifies that the pair of tenant and company ID exists together. A foreign key on company ID alone would only establish global existence. It would not establish tenant consistency. The primary key here uses a source record number for demonstration; a reusable importer needs an import/job namespace or a stable approved business identity to distinguish separate files.

Application authorization and RLS are still necessary where the application depends on them. Referential integrity is not a complete access-control system.

Import parents first and preserve the ID map

When importing both parents and children, make the parent identities available first. Insert the approved parent set, capture or resolve the resulting ID map, then translate child references using that map. Keep the namespace tenant-scoped and tied to the source system.

Do not assume generated IDs are consecutive, match CSV row order or can be reconstructed from the first returned ID plus an offset. Concurrent transactions, failed attempts and sequence behavior break those assumptions. Use explicit key-to-ID correspondence.

A one-time script can hold a reviewed map within a transaction when the dataset is small. A resumable migration needs durable mapping evidence that survives a process restart and prevents a retry from creating a second parent. Its retention and access policy should reflect the source data it contains.

If the source contains several representations of the same parent, decide how to deduplicate them before creating children. This is where a migration can grow beyond a single-table import. The identity and default guide covers ID-supply rules separately.

Nullable, second-pass and deferred relationships

A nullable foreign key can legitimately mean “no company assigned.” It should not silently mean “the supplied company could not be found.” Keep those outcomes separate in the source review and result.

A second pass can fill a reference after both rows exist, but only when the target allows the temporary NULL and the application can tolerate it. If each pass commits separately, the intermediate state is visible and can trigger workflows. If both passes are in one transaction, database state becomes visible together at commit, but you must still understand triggers and external effects.

A deferrable foreign key can postpone its check until later in the same transaction. It does not manufacture a missing referenced row, make the column nullable or let a violation remain at commit. Only constraints declared DEFERRABLE can have their timing deferred; NOT NULL and CHECK constraints are not deferred this way in PostgreSQL 17. See SET CONSTRAINTS.

Cyclic relationships need a specific schema and transaction plan. Options may include valid nullable states, deferred references or a domain-level redesign. Disabling foreign keys is not a general recovery procedure. Test the selected strategy against the actual application invariants.

Verify the relationship after import

Check that every accepted source record is accounted for and that its resolved reference matches the reviewed mapping. Count missing records separately from records attached to the wrong parent. A database can enforce a valid reference while the importer chooses the wrong valid company.

Preflight lookups can become stale. Keep final constraints and execute the mapping/write with an appropriate transaction strategy. Decide how parent deletion, reference reassignment or a competing import is handled. For a controlled one-time migration, coordinating the write window may be simpler than building a concurrent migration service.

Use the post-import verification checks to compare the accepted identities and important fields, then exercise the application’s ordinary relationship views.