Databases
Database Foreign Key Cleanup: Retire Relationships After Product Flows Move
A stale foreign key usually appears after a product flow stops treating two records as tightly connected. The application no longer writes the relationship, new screens read from another table, and the constraint starts to look like leftover schema. Removing it can still be risky because foreign keys often protect behavior that is easy to forget: cascade rules, importer assumptions, backfills, support tools, and reports that depend on referential integrity even after the main product path moved.
Use this cleanup when a foreign key constraint creates migration friction, blocks archival work, slows writes, or no longer matches the domain model. The goal is not to drop constraints faster. The goal is to prove which integrity rule replaced the old relationship and to leave behind a schema that still makes bad data hard to create.
Evidence before touching the constraint
Start with the relationship, not the table. Name the parent table, child table, column, delete behavior, and the feature that originally needed the constraint. Then look for current readers and writers that still assume the relationship is guaranteed.
| Evidence area | Subject-specific check | What it proves |
|---|---|---|
| Orphan risk | Count child rows with missing parents in a restored or read-only replica | Whether the application already allows broken references |
| Delete behavior | Inspect cascade, restrict, and set-null rules | Whether removing the key changes cleanup side effects |
| Import paths | Review bulk loaders, customer migrations, and replay jobs | Whether data enters outside normal application validation |
| Reporting | Search warehouse models, BI queries, and support exports for joins on the key | Whether analysts still rely on guaranteed joins |
| Replacement rule | Find application validation, new constraints, or domain events that supersede the key | Whether integrity moved somewhere real |
A useful first query is narrow and read-only:
SELECT COUNT(*) AS child_rows_without_parent
FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;This shows whether orphaned rows already exist for one relationship. It does not prove the constraint is unused, and it does not prove deletion is safe.
Why recent silence is misleading
Foreign keys are often most important during abnormal work: customer data imports, incident repair, test fixture setup, rollback scripts, and archive jobs. If you only inspect the latest application writes, you may miss the paths that use the constraint as a guardrail.
Do not rush when the constraint protects billing records, entitlement checks, audit history, or customer-owned data. Also slow down when the product migration changed cardinality. A relationship that moved from one-to-many to many-to-many often needs a new invariant, not merely less schema.
A safe retirement sequence
First, document the replacement invariant. If the relationship is now optional, make that explicit in application code, API docs, and reporting models. If another table now owns the relationship, prove that writes cannot split between the old and new paths.
Second, run orphan checks across the longest realistic data cycle. Include recently restored backups or replicas if import and repair workflows are part of the risk. Third, remove application assumptions before removing the database constraint. A code path that still expects referential errors as validation should fail in tests before the migration reaches production.
Finally, treat the schema migration as the last step. The decision record should include the old constraint name, owner, replacement rule, orphan query, rollback plan, and waiting window.
Prevent the next stale relationship
When adding a foreign key, require an owner and a retirement condition in the migration note. If the relationship exists only during a transition, name the target release or data backfill that should trigger review. For temporary compatibility relationships, put the sunset check in the migration backlog, not a separate cleanup spreadsheet.
Key takeaways
- Foreign key cleanup is integrity cleanup, not only schema cleanup.
- Delete behavior, import paths, and reporting joins matter as much as product reads.
- A read-only orphan query is a useful signal, but it is not a full safety case.
- Prevention starts when the relationship is created, with ownership and exit criteria recorded in the migration.