Foreign Keys and Referential Integrity: The Guardrails That Keep Your Data Honest

A foreign key is a column-level constraint that ties one table's row to a specific row in another table, and referential integrity is the guarantee the database makes to enforce it: every reference has to resolve to something that actually exists. Without that constraint, your application is trusting itself to never delete the wrong thing, in every code path, forever.

Quick answer: A foreign key forces a column to hold only values that exist as a primary key somewhere else. Referential integrity is the database — not your application code — refusing to let a task, order, or comment point at a parent row that's already been deleted.

What Referential Integrity Actually Means

Referential integrity means every foreign key value in a table must match an existing primary key value in the table it references, with no exceptions for which app, script, or admin panel wrote the row. It's a rule the database itself refuses to break, which is exactly why it holds up better than a rule your team promises to remember.

This isn't a new idea bolted onto modern databases. E.F. Codd, who defined the relational model in his 1970 paper, formalized referential integrity a decade later as one of two integrity rules a relational database must uphold — alongside entity integrity, which says a primary key can never be null or duplicated. Referential integrity was the second rule: a foreign key value must either match a real primary key or be null.

The rule in one sentence

A child row may only reference a parent row that exists in the database at that exact moment. Not "existed when the app checked five seconds ago." Exists, right now, guaranteed by the same engine that's about to commit your write.

Why the database enforces it, not the team

Modern products write to the database from more places than the main API:

• The primary web and mobile app
• Background jobs and queues
• CSV or bulk imports
• Internal admin tools
• Third-party integrations and webhooks
• Direct SQL run by an engineer during an incident

Every one of those entry points would need to independently check "does this project still exist?" before writing a task. Miss one, and you have a bug that only shows up under specific, hard-to-reproduce conditions. A foreign key constraint moves that check into the schema itself, so it applies uniformly no matter which of the six lists above touched the row. This is the same discipline covered in the complete guide to data modeling: the schema does the enforcing so your team doesn't have to remember to.

The Orphan Record Bug: What Happens When the Foreign Key Is Missing

An orphan record is a child row whose parent has been deleted or changed, leaving a reference that points at nothing — a task with project_id = 47 when project 47 no longer exists. Without a foreign key constraint, the database will create, store, and serve that broken reference indefinitely, and nothing will complain until something downstream tries to use it.

Picture a simple project-tracking schema without any constraint linking the two tables:

CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  project_id INTEGER,   -- no constraint, just an integer
  title TEXT NOT NULL
);

Nothing here stops DELETE FROM projects WHERE id = 47 from succeeding while forty tasks still carry project_id = 47. The delete looks clean in the logs. The damage shows up later — a dashboard query joins tasks to projects and silently drops the orphaned rows, or worse, a detail page crashes because it expected a project name that no longer exists.

Now compare that to the same schema with a constraint in place:

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  project_id INTEGER REFERENCES projects(id),
  title TEXT NOT NULL
);

The instant someone tries to delete a project with active tasks, the database rejects it (or cascades, or nulls the reference — the choice is yours, covered next). The bug becomes impossible to ship rather than a support ticket waiting to happen.

The deeper reason this bug is so common is that teams often don't treat tasks and projects as fully separate entities with their own integrity rules until something breaks. Deciding that a task deserves to be its own entity rather than a nested field on a project record is what makes a foreign key constraint possible — and necessary — in the first place. Once you draw that boundary, the moment a customer opens a task page and sees a blank project name isn't a minor rendering bug; mapped against a customer journey, it's usually the exact point where trust in the product takes a hit.

ON DELETE Options Are Product Decisions, Not Just Syntax

ON DELETE RESTRICT, CASCADE, and SET NULL tell the database what to do to child rows when a parent row is deleted, and each one encodes a different product decision: block the delete, delete the children too, or let them survive without their parent. Choosing between them is a requirements conversation, not a line engineers should pick alone while writing a migration.

RESTRICT: when losing data would be worse than blocking a click

RESTRICT (or its close cousin NO ACTION, which most databases default to when you specify nothing) is the conservative choice: the database simply refuses the delete. This forces a product conversation that often gets skipped — what does the user actually see when their delete is blocked? "Can't delete this project — it has 12 billed hours attached" is a UX requirement hiding inside a database error, and it deserves the same design attention as any other error state.

CASCADE: when children have no independent identity

CASCADE fits composition relationships — a task doesn't mean anything without its project, a comment doesn't mean anything without its post. But cascades have a blast radius that's easy to underestimate. Martin Fowler and Pramod Sadalage, in Refactoring Databases: Evolutionary Database Design, caution that cascading deletes across a schema shared by multiple applications can quietly destroy data that a different team's feature depended on. Cascade the relationship you drew, not every relationship that happens to share a table.

SET NULL: when the relationship is optional, not existential

SET NULL fits association relationships — a ticket is still a real, useful record even after the employee it was assigned to leaves the company. It only works if the foreign key column was designed to allow nulls in the first place, which is a decision made when you're choosing column types for every attribute, not something you can retrofit after a delete throws a constraint violation.

Choosing the Right ON DELETE Behavior for Your Domain

Choosing between RESTRICT, CASCADE, and SET NULL comes down to one question per relationship: does the child row mean anything without its parent? If no, cascade. If yes, but the link is optional, set null. If yes, and losing the link would hide real business history, restrict.

Run each relationship in your schema through this sequence during spec review, before a migration file makes the decision by default:

1. Name the relationship's meaning, not just its shape. "A task belongs to a project" is composition. "A ticket is assigned to an employee" is association. The wording tells you which column of the table above applies.
2. Ask what happens to the user's mental model. This is really a Jobs to Be Done question in disguise — what job is the user hiring the delete button to do, and does silently cascading their child records help that job or quietly sabotage it?
3. Check for a paper-trail requirement. Anything with financial, legal, or audit weight — invoices, orders, signed approvals — leans toward RESTRICT almost by default.
4. Write the ON DELETE decision into the spec next to the entity relationship, not into a ticket comment written after a production incident already happened.
5. Test the actual delete path everywhere, including admin tools and bulk scripts. A cascade or restrict rule that only applies to the main UI isn't a guarantee — it's a suggestion with exceptions.

Referential Integrity Beyond DELETE

Foreign keys also govern updates through ON UPDATE CASCADE, whether a relationship is required or optional via NOT NULL versus a nullable column, and multi-column composite keys — and nearly all of that enforcement disappears the moment the parent and child tables live in separate databases or services.

ON UPDATE CASCADE matters less often than ON DELETE, mostly because most schemas key on surrogate values (auto-increment integers or UUIDs) that never change. It becomes relevant when a table uses a natural key as its primary key — an email address or a SKU — and that value gets edited; without the cascade, every child row's reference silently goes stale.

Whether a foreign key column is NOT NULL decides whether the relationship is mandatory or optional: every task must have a project, but a ticket might sit unassigned. That's a decision made at the same moment you're choosing column types and nullability for every attribute in the schema, not something to patch in after the fact.

Composite foreign keys — a constraint spanning more than one column, like (tenant_id, project_id) referencing (tenant_id, id) — show up constantly in multi-tenant SaaS schemas, which is why tenant_id ends up threaded through nearly every table's constraints, not just its own dedicated table.

Cross-service references are where referential integrity quietly stops being the database's job. If projects lives in one service's database and tasks lives in another's, there is no engine that can enforce a constraint across that boundary — you're back to the app-enforced risk from earlier in this article, unless you build compensating patterns like an outbox pattern, a saga, or a periodic reconciliation job. Eric Brewer's CAP theorem is the formal reason this trade-off exists: distributed systems that split data across services are choosing availability and partition tolerance over the kind of instant, always-consistent guarantee a single-database foreign key gives you for free.

Where This Shows Up in Prodinja's Data Modelling Tool

Relationships you draw in Prodinja's Data Modelling tool become foreign-key constraints in the generated DDL — not a note in a doc that engineering has to interpret and re-implement later. Drag a line from Task.project_id to Project.id, choose whether a deleted project should restrict, cascade, or null out its tasks, and that choice is the same one that ends up enforced by the database.

The practical effect is that the ON DELETE conversation from the sections above happens once, at design time, instead of getting decided implicitly by whatever a migration file defaults to. It's the natural extension of treating the entity-relationship diagram as the actual product rather than a whiteboard sketch that gets reinterpreted on the way to code — the integrity guarantee becomes explicit on the diagram instead of assumed in someone's head.

Key Takeaways

• Referential integrity is enforced by the database, not remembered by the team — Codd built it into the relational model specifically so no single client has to be the one that gets it right every time.
• Missing foreign keys don't cause immediate errors; they cause orphan records that surface later as a crash, a blank field, or a support ticket.
• RESTRICT, CASCADE, and SET NULL are product decisions about what should happen to children when a parent disappears — not backend trivia engineers should decide unilaterally.
• Composition relationships lean cascade; association relationships lean restrict or set null. Ask whether the child means anything without its parent before picking either.
• Nullable versus NOT NULL on a foreign key column decides whether a relationship is optional or mandatory, and that's a data-modeling decision, not a migration afterthought.
• Referential integrity mostly disappears across service or database boundaries, which is why cross-service references need compensating patterns like reconciliation jobs.
• Drawing the relationship explicitly, and generating the constraint from it, closes the gap between what the diagram promises and what the database actually enforces.