Why Your Foreign Keys Might Be Lying to You: Referential Integrity vs Application Logic

The Report That Couldn't Find Its Customers

Finance runs the monthly revenue report and it crashes on a null pointer. Somebody digs in and finds 312 orders whose customer_id points at customers that don't exist. The order service has always checked that the customer exists before creating an order. There's a unit test for it. Code review would never let an insert through without that check.

So where did the orphans come from? A few came from a race: a customer was deleted in the half-second between the order service checking for them and inserting the order. Most came from a cleanup script someone ran last spring to purge test accounts. It deleted customers directly with SQL, so the application's "don't delete customers with orders" rule never ran.

The schema had no foreign key on orders.customer_id. The team had decided years ago that the application handled it. That decision held as long as every write went through one code path, one at a time, and no write ever has.

Two Ways to Say "This Must Exist"

Both say the same thing. The difference is who checks, when, and for which writers. Those three differences are where application checks quietly stop working.

Gap One: Check-Then-Act Is a Race

An application check reads a fact and then acts on it as if the fact is still true. Under the default isolation level in PostgreSQL, MySQL, and most other databases, nothing stops another transaction from changing that fact in between.

Here's the interleaving that produces an orphan:

1. Transaction A (order service) runs SELECT 1 FROM customers WHERE id = 42 and finds the customer.
2. Transaction B (account deletion) checks that customer 42 has no orders, finds none, and deletes the customer.
3. B commits.
4. A inserts an order for customer 42 and commits.

Each transaction did exactly what it was written to do, and B's own check ran correctly too. It looked for orders, found none (A's insert didn't exist yet), and deleted. The two checks can't see each other's in-progress work, so both pass.

A foreign key closes this gap with locking. In PostgreSQL, when you insert a child row, the database locks the referenced parent row with FOR KEY SHARE. A DELETE of that parent needs a lock that conflicts with it. Whichever transaction gets there second has to wait for the first to finish, then sees the result:

If the delete committed first, the insert fails with insert or update on table "orders" violates foreign key constraint. If the insert committed first, the delete fails (or cascades, depending on how you declared the key). Either way, no orphan.

Gap Two: Not Every Write Goes Through Your App

The race is the subtle problem. The common one is simpler: an application check only protects the code path it's written in.

Over a system's life, writes come from far more places than the service that owns the table: a bulk import job, a backfill in a migration, a support engineer fixing data in a SQL console, a second service someone gave database credentials to "temporarily," an ORM call that skips the model's validation hooks. None of them run your check. Only a constraint in the database sits on every path.

There's also a third, quieter gap: the delete side. Most applications check that the parent exists when creating a child. Far fewer check for children when deleting a parent, and fewer still when updating a parent's key. A foreign key covers all three directions.

Try It: Race the Two Transactions

The simulator runs the two transactions from earlier. Transaction A places order 901 for customer 42 while transaction B deletes customer 42. Choose who enforces the rule, whether the transactions run one after another or concurrently, and whether B goes through the app or runs as a SQL script.

Some combinations worth trying:

• App check, one after another, through the app. This is the case your tests cover, and it works: B sees the order and refuses.
• App check, concurrent. Both checks pass and you get an orphan. Neither transaction did anything wrong on its own.
• App check, one after another, SQL script. No concurrency needed. The script never ran the check.
• FK · RESTRICT, either timing. No orphan in any combination. When the transactions overlap, A waits on B's row lock and then fails with an error the app can handle.
• FK · CASCADE, one after another. The data is consistent, but deleting the customer deleted the order with it. That might be what you want, or it might have just deleted someone's paid invoice.

Gap Three: Even Real Foreign Keys Can Lie

Adding foreign keys doesn't end the story. There are several ways a schema can show a constraint while the data still doesn't satisfy it.

When you inherit a database, don't assume the declared constraints describe the data. A quick orphan check settles it:

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL
AND c.id IS NULL;

-- PostgreSQL: list constraints that were never validated
SELECT conrelid::regclass AS table_name, conname
FROM pg_constraint
WHERE contype = 'f' AND NOT convalidated;

Choosing What Happens on Delete

Once you have a foreign key, you have to decide what happens when someone deletes (or re-keys) the parent. This choice matters as much as having the constraint.

Foreign Keys Aren't Free

The usual reasons teams skip foreign keys are performance and flexibility. Some of those concerns are real; most have a fix.

Write overhead. Every child insert looks up the parent through its primary key index. That's one index lookup per row, which is rarely noticeable unless you're bulk-loading millions of rows. Even then, you can load first and add the constraint with validation afterward.

The missing child index. This one causes real outages. PostgreSQL does not create an index on the referencing column. When you delete a customer, the database has to find that customer's orders, and without an index on orders.customer_id that's a sequential scan of the whole orders table, for every deleted customer. InnoDB creates the index automatically; PostgreSQL leaves it to you. (See Why Your Index Isn't Being Used if you've added it and it still isn't helping.)

Lock contention on hot parents. Because each child insert takes a share lock on the parent row, thousands of concurrent inserts referencing the same parent (one tenant row, one popular product) all lock that row. Share locks don't block each other, but they conflict with updates to the parent's key, and in PostgreSQL many simultaneous share lockers are tracked through a structure called MultiXact that can become a bottleneck at very high rates. If you hit this, it shows up in lock wait metrics and is worth measuring before you redesign around it.

Constraints across boundaries. A foreign key can only reference a table in the same database. Once customers live in one service's database and orders in another's, or the two tables are on different shards, there's nothing to declare. Many sharded systems drop foreign keys for this reason. (Database Sharding: When One Database Becomes Many covers why cross-shard guarantees get expensive.)

That last case is the one where application logic really is the only option, and you need to design for it rather than hope.

When the Database Can't Enforce It

Across service or shard boundaries, accept that references can dangle for some period, and build the system to notice and handle it:

• Validate at write time, knowing it's a best effort. Calling the customer service before creating an order catches most mistakes. It can't stop a delete that happens a second later.
• Don't hard-delete referenced entities. Have the customer service publish a "customer deleted" event and keep a tombstone, so other services can react and lookups return "deleted" rather than "never existed."
• Run a reconciliation job. Periodically compare references against the source of truth and flag or repair orphans. Without it, you won't find out about dangling references until something like the revenue report breaks.
• Make readers tolerate missing parents. Code that follows a cross-service reference should handle "not found" deliberately instead of crashing.

For multi-step operations across services that need to be undone together, see The Saga Pattern.

Where Each Belongs

Application checks and foreign keys solve different problems, so most systems need both:

Use the application check for the user experience and for business rules: check early, return a clear message, and avoid most constraint errors in the first place. Use the foreign key as the guarantee: it catches the race you didn't anticipate and the script you didn't know about. When the application check misses something, the foreign key turns it into an error instead of corrupted data.

Key Takeaways

• An application check is check-then-act. Under default isolation, a concurrent delete can land between the check and the insert, and both transactions' checks pass.
• Application checks only cover their own code path. Scripts, imports, consoles, and other services skip them. A foreign key is enforced on every write to the table.
• Foreign keys use locks to stay correct. The child insert locks the parent row, so the conflicting transaction waits and then fails cleanly.
• Declared doesn't mean enforced. Disabled checks, NOT VALID constraints, ignored inline REFERENCES, NULLs, and soft deletes all leave constraints that don't match the data. Run an orphan query on any database you inherit.
• Choose ON DELETE on purpose. CASCADE is for parts of a whole, not for business records.
• Index the referencing column. PostgreSQL doesn't do it for you, and parent deletes scan the child table without it.
• Use both. The app check gives users a clear message; the foreign key guarantees the data. Across service boundaries, where no constraint exists, plan for dangling references and reconcile them.

References

• PostgreSQL docs: Foreign Keys
• PostgreSQL docs: Row-Level Locks, including FOR KEY SHARE
• PostgreSQL docs: ALTER TABLE, for NOT VALID and VALIDATE CONSTRAINT
• MySQL docs: FOREIGN KEY Constraints, including foreign_key_checks