Learning center

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.

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;
relmode
productsAccessShareLock
productsShareRowExclusiveLock
line_itemsAccessShareLock
line_itemsShareRowExclusiveLock

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 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 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 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);
idproduct_id
11023

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;
relmode
productsAccessShareLock
productsRowShareLock
line_itemsAccessShareLock
line_itemsShareUpdateExclusiveLock

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.

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.

Start free with GitHub

Related reading