Do you really need foreign keys?

Original Article ↗ Hacker News Discussion ↗

Whether to enforce foreign key constraints in relational databases pits data integrity and long‑term maintainability against write performance, sharding flexibility, and migration complexity. Many engineers argue that constraints should be the default because they prevent subtle bugs, document the data model, and protect data that will outlive any single application, while others point to large MySQL deployments and specialized architectures where constraints are dropped and integrity is enforced in application code instead. The exchange highlights that foreign keys are rarely a bottleneck at typical scales, and that choosing to omit them safely demands high discipline, strong tooling, and a clear understanding of the trade‑offs.

Overall stance on foreign key constraints

  • Strong majority view: use foreign key (FK) constraints by default for any non-trivial, long-lived system.
  • They prevent corrupt/orphaned data, expose app bugs early, and make refactoring safer.
  • Several accounts of large legacy MySQL / multi-hundred-table systems without FKs becoming “data rot” nightmares, requiring brittle cleanup scripts and institutional knowledge.

Data integrity, safety, and documentation

  • FKs are framed like types, seatbelts, or protected memory: you can code without them, but they catch many mistakes cheaply.
  • With strong constraints, teams can assume “if it’s in the DB, it’s valid,” treating the DB as a fortress.
  • FKs provide living documentation of the data model; missing FKs make it hard to understand relationships or even tell if a column is a reference at all.

Performance, scale, and when to consider dropping FKs

  • Critics argue FKs slow heavy write/delete workloads, complicate sharding and online schema changes (especially in MySQL), and add lock contention.
  • Others counter that:
    • Most apps never reach a scale where this matters.
    • You pay the integrity cost somewhere; moving checks into application code doesn’t make them free and risks races.
    • Batch processing, deferred constraints, and tuning often mitigate performance issues.
  • Some very high-scale or complex platforms implement custom integrity layers instead of native FKs, but this is portrayed as an advanced, specialized choice.

Application-level enforcement vs DB-level

  • One camp: integrity can be enforced in the app or service layer, especially if only one writer and strict access control; FKs are optional.
  • Opposing camp: multiple apps, ad-hoc access, and concurrency semantics make reproducing DB guarantees in code fragile and error-prone.

Soft deletes and deletion behavior

  • Combining deleted_at soft deletes with FKs is tricky, especially ensuring parents with active children cannot be soft-deleted.
  • Proposed approaches: universal soft-delete, composite FKs including an “is_deleted” flag, moving deleted rows to separate tables, or audit tables via triggers.

Environment strategies

  • Some suggest enabling FKs only in dev/test for catching issues while disabling in prod for ingestion speed.
  • This is heavily criticized as backwards and risky: production, not test, holds the data that truly matters.