Adding a Foreign Key to a Legacy Orders Table Full of Orphan Rows

This one comes up whenever an old application gets a second look. The orders table has a customer_id column. It has always pointed at customers.id. Nobody ever declared that as a foreign key, because the original developer said the app handles it.

The app mostly did. Over the years a few customers were deleted by hand, a data import skipped some rows, and now a handful of orders point at customers that don’t exist. Nobody knows how many.

The team decides to make the relationship real:

foreign_keys:
- name: orders_customer_fkey
  columns: [customer_id]
  references:
    table: customers
    columns: [id]
  on_delete: restrict

Reading the plan

./bin/forgeschema plan --explain current.json desired.yaml

The plan puts add_foreign_key last, after every table, column and referenced key exists. Its explanation says PostgreSQL will check every existing row and fail if any has no match. The lock list shows two tables. orders gets ACCESS EXCLUSIVE because it’s changing. customers gets SHARE ROW EXCLUSIVE, the mode PostgreSQL itself needs to add a reference to it, which blocks writes to customers but not reads.

So for the length of that check, new sign-ups wait and customer pages still load. Worth knowing before you pick a time.

When the orphans are found

Apply it without cleaning up and PostgreSQL finds the first orphan, refuses the constraint, and ForgeSchema rolls the whole migration back. The error carries PostgreSQL’s message and the SQLSTATE, 23503 for a foreign key violation, without the detail line that would quote the offending row values.

That’s the useful failure. The database is exactly as it was. You now know orphans exist, so you go and decide what they are. Archive them, reattach them to a placeholder customer, or delete them. ForgeSchema doesn’t write application rows, so that decision and that SQL are yours.

The reviewed plan is still valid, since the failed run committed nothing. Apply it again and verify:

./bin/forgeschema apply reviewed-plan.json
./bin/forgeschema verify --plan reviewed-plan.json

From then on, PostgreSQL enforces what the app only promised. Verify will also notice if someone later drops the key or changes its ON DELETE action, since foreign keys and their actions are part of what it compares.

Foreign keys stay within one schema in this alpha. A reference to a table in another schema is outside the supported contract and gets rejected at inspection.