How to Handle Orphan Records in a SQL Query with a Foreign Key

Why the foreign key crashes the whole import

The orders table has a foreign key that ties every order back to a real customer:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_ref VARCHAR(50) NOT NULL,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    product VARCHAR(200) NOT NULL,
    quantity INTEGER NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    order_date DATE NOT NULL
);

The CSV carries customer_ids that do not exist in customers. The seeded customers are ids 1, 2, 3, 5, 7, 8 - but orders.csv references 4, 6, and 9 as well. The moment the import tries to insert REF-004 (customer_id 4), PostgreSQL rejects it:

psycopg2.errors.ForeignKeyViolation: insert or update on table "orders"
violates foreign key constraint "orders_customer_id_fkey"

Because the original script does one straight INSERT per row with no per-row error handling, that exception aborts the transaction and the script. Every valid order gets rolled back with it - the table ends up with zero rows. First confirm which ids are real:

# Customer ids that actually exist
psql -h localhost -U postgres -d app -c "SELECT id FROM customers ORDER BY id;"
# customer_ids referenced in the CSV - spot the orphans
cut -d, -f2 orders.csv | tail -n +2 | sort -n | uniq -c

Check the foreign key before you insert

The robust pattern is proactive: load every valid customer id into a Python set once, then check each row's customer_id against that set before inserting. Orphans get logged and skipped; valid orders still land.

#!/usr/bin/env python3
import csv, sys, psycopg2

DB_URL = "postgresql://postgres:postgres@localhost:5432/app"

def import_orders(csv_path):
    conn = psycopg2.connect(DB_URL); cur = conn.cursor()
    cur.execute("SELECT id FROM customers")
    valid_ids = {r[0] for r in cur.fetchall()}
    loaded = skipped = 0
    with open(csv_path) as f:
        for row in csv.DictReader(f):
            cid = int(row["customer_id"])
            if cid not in valid_ids:
                print(f"Skipping {row['order_ref']} - unknown customer_id {cid}")
                skipped += 1; continue
            cur.execute(
                """INSERT INTO orders (order_ref, customer_id, product, quantity, total, order_date)
                   VALUES (%s, %s, %s, %s, %s, %s)""",
                (row["order_ref"], cid, row["product"], int(row["quantity"]),
                 float(row["total"]), row["order_date"]))
            loaded += 1
    conn.commit(); cur.close(); conn.close()
    print(f"Imported {loaded} orders ({skipped} skipped)")

if __name__ == "__main__":
    import_orders(sys.argv[1] if len(sys.argv) > 1 else "orders.csv")

Preloading the ids into a set means the check is a fast in-memory lookup, not a query per row. Run it and you get a clean summary instead of a traceback:

python3 import_orders.py orders.csv
# Skipping REF-004 - unknown customer_id 4
# Skipping REF-006 - unknown customer_id 6
# Skipping REF-009 - unknown customer_id 9
# Imported 7 orders (3 skipped)

Confirm the load and the skipped orphans

Seven valid orders should now be in the table, and no row should reference a missing customer:

SELECT COUNT(*) FROM orders;                              -- 7
SELECT COUNT(*) FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id);  -- 0

The orphan customer_ids point at a real upstream problem - a customer that was never created, or a bad export. Skipping keeps the pipeline running, but log the skips so the data team can chase the source. In production you would run the check and insert inside one transaction, or tag each row with an import-batch column so a re-run can delete the batch and reload it idempotently.