---
title: "Adding a PostgreSQL foreign key to a table with orphaned rows | Proofline"
description: "Adding a foreign key locks two tables: the one that gets the constraint and the one it references."
url: https://proofline.sh/learn/postgres-foreign-key-existing-rows/
---

[Learning center](/learn/)

# Adding a PostgreSQL foreign key to a table with orphaned rows

Adding a foreign key locks two tables: the one that gets the constraint and the one it references. A plain ADD FOREIGN KEY scans every existing row while blocking writes to both. Add it as NOT VALID, find the orphaned rows, and validate it separately under a lock that lets writes continue.

Breaking migrations The Proofline team November 20, 2026 3 min read

## Line items that point at a deleted product

An order system never declared the relationship between line items and products. A product was deleted years ago, and one line item still points at it. We ran this on PostgreSQL 16.15.

```
CREATE TABLE products (id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE line_items (id integer PRIMARY KEY, product_id integer NOT NULL);
INSERT INTO products VALUES (1, 'kettle'), (2, 'mug');
INSERT INTO line_items VALUES (100, 1), (101, 2), (102, 3);

ALTER TABLE line_items
    ADD CONSTRAINT line_items_product_fk
    FOREIGN KEY (product_id) REFERENCES products (id);
```

```
ERROR:  insert or update on table "line_items" violates foreign key constraint "line_items_product_fk"
DETAIL:  Key (product_id)=(3) is not present in table "products".
```

Like other constraint failures, this one names the first violation PostgreSQL meets, not all of them. On a large table it arrives after a scan that held its locks the whole time.

## See which locks the statement takes

Add the constraint as `NOT VALID` inside a transaction and read `pg_locks` before committing:

```
BEGIN;
ALTER TABLE line_items
    ADD CONSTRAINT line_items_product_fk
    FOREIGN KEY (product_id) REFERENCES products (id) NOT VALID;

SELECT relation::regclass AS rel, mode FROM pg_locks
WHERE locktype = 'relation' AND pid = pg_backend_pid()
  AND relation::regclass::text IN ('products', 'line_items');
COMMIT;
```

| rel | mode |
| --- | --- |
| products | AccessShareLock |
| products | ShareRowExclusiveLock |
| line\_items | AccessShareLock |
| line\_items | ShareRowExclusiveLock |

`SHARE ROW EXCLUSIVE` conflicts with the `ROW EXCLUSIVE` lock that every `INSERT`, `UPDATE` and `DELETE` takes, so writes to both tables wait until the transaction ends. The [ALTER TABLE documentation](https://www.postgresql.org/docs/16/sql-altertable.html) states that a foreign key needs this lock on the referenced table as well. With `NOT VALID`, PostgreSQL skips the scan and the lock lasts a moment. Without it, writes to `products` wait for a scan of `line_items`. A long transaction on either table can still hold the statement up, and queries queue behind it; [why a fast migration blocked every query](/learn/postgres-migration-lock-queue/) shows how to bound that wait.

## What the unvalidated constraint enforces

From the moment the constraint commits, new rows must reference a product:

```
INSERT INTO line_items VALUES (103, 4);
-- ERROR:  ... violates foreign key constraint "line_items_product_fk"
-- DETAIL:  Key (product_id)=(4) is not present in table "products".

UPDATE line_items SET id = 1102 WHERE id = 102;
-- UPDATE 1
```

The update succeeded even though row 102 is orphaned. PostgreSQL rechecks a foreign key on update only when the referencing columns change. A `NOT VALID` `CHECK` constraint behaves differently: it rejects any update to a violating row, as [what NOT VALID means for CHECK constraints](/learn/postgres-not-valid-constraints/) shows. With a foreign key, the previous application release keeps working on old orphaned rows as long as it does not write a missing product ID.

## Find the orphans, then validate

```
SELECT li.id, li.product_id
FROM line_items li
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.id = li.product_id);
```

| id | product\_id |
| --- | --- |
| 1102 | 3 |

Each orphan needs a decision: restore the product, point the row at a placeholder, or archive the line item. For this example we restore the product, then validate:

```
INSERT INTO products VALUES (3, 'teapot (restored)');

BEGIN;
ALTER TABLE line_items VALIDATE CONSTRAINT line_items_product_fk;
-- read pg_locks as above
COMMIT;
```

| rel | mode |
| --- | --- |
| products | AccessShareLock |
| products | RowShareLock |
| line\_items | AccessShareLock |
| line\_items | ShareUpdateExclusiveLock |

Neither lock conflicts with ordinary inserts, updates or deletes, so the validation scan runs while the application writes to both tables.

## What to rehearse

Run the orphan query against a replica with production data and record its count in the pull request. Then rehearse the three steps on a copy: add the constraint as `NOT VALID` with a `lock_timeout`, fix the orphans, and validate. Time the validation, because it reads both tables. Check that the referencing column has an index too. Without one, every delete or key update on `products` scans `line_items` to look for references.

Find the other changes that need a rehearsal in [which PostgreSQL migration needs a rehearsal](/learn/postgres-migration-rehearsal/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [What NOT VALID means for PostgreSQL CHECK constraints](/learn/postgres-not-valid-constraints/)
    
    Breaking migrations The Proofline team November 6, 2026 7 min read
    
-   ### [Why a fast PostgreSQL migration blocked every query](/learn/postgres-migration-lock-queue/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
