Foreign Key Discipline

Use FKs; trade-offs.

Overview

Foreign-key discipline uses database FKs to enforce referential integrity at the engine level rather than relying on ORM checks or application logic. ORM-level enforcement misses anything that bypasses the ORM (raw SQL, batch scripts, restored backups); FKs catch all of it. The discipline is in adopting FKs by default, always indexing the FK column, choosing ON DELETE behavior deliberately rather than letting the ORM pick, and managing FK validation carefully during migrations.

• Use FKs; understand tradeoffs. Database-enforced referential integrity catches application bugs that ORM checks miss.
• Indexed FK columns. Always index the FK column; FK lookups happen on every dependent query and the index matters.
• ON DELETE behavior. CASCADE for owned children, SET NULL for optional references, RESTRICT for required references; the choice encodes business logic.
• Partition tradeoffs plus migration considerations. FKs do not cross partitions in some engines; FK validation locks tables on add, so use NOT VALID then VALIDATE for big tables.

The approach

The practical approach is FK by default for any reference between tables, always index the FK column at creation, choose ON DELETE deliberately based on the parent-child semantics, use NOT VALID then VALIDATE on Postgres for adding FKs to large tables without table locks, and document the per-FK rationale so the relationship semantics survive team turnover.

• FK by default. Enforce at database level; FKs catch what ORM checks miss when batch scripts or restores bypass the application.
• Indexed FK columns. Always index; FK lookups happen on every dependent query.
• Choose ON DELETE deliberately. CASCADE for owned children, RESTRICT for required references; the choice encodes business logic.
• NOT VALID for migrations plus documented relationship. Postgres NOT VALID then VALIDATE for adding FKs without table locks; per-FK rationale committed to the schema documentation.

Why this compounds

FK discipline compounds across the lifetime of the schema. Each enforced relationship catches application bugs the next reviewer would not have spotted; each indexed FK keeps dependent-query performance bounded; the team builds a vocabulary for relationship semantics that pays off on every new table. Without the discipline, referential integrity bugs surface as customer-reported data anomalies months after introduction.

• Data integrity. Database-level enforcement catches bugs at write time; the data stays consistent regardless of which path produced it.
• Engineering quality. Bugs caught early; the FK violation surfaces in CI rather than in customer reports.
• Operational fit. Right ON DELETE supports workflows; the cascade and restrict choices match the actual business logic.
• Institutional knowledge. Each FK teaches the data model; the relationships in the schema become readable rather than implicit.

FK discipline is a database discipline that pays off across years. Nova AI Ops integrates with database telemetry, surfaces relationship patterns, and supports the team’s schema design discipline.