Declare foreign key constraints wherever your database and your migration tooling allow them. There are real cases where you cannot, and in those cases what you give up is the enforcement, not the relationship: the dependency between orders.customer_id and customers.id exists whether or not the database checks it, and someone still has to be able to see it.

That second half is the part the usual debate leaves out. The argument about foreign keys is almost always framed as integrity against write performance, and it skips what happens to everyone who reads the schema afterwards. On Microsoft’s Q&A forum, a user asked why they can’t see table relationships while creating an ER diagram for a production SQL Server database. The accepted answer is the whole problem in one line: the diagram shows relationships “if Primary/Foreign key relationship does exist.” Another reply is shorter still: “Without the design can’t know the relations.”

Should I use foreign key constraints?

Yes, by default. A declared foreign key is the one integrity check that cannot be forgotten by a code path, a backfill script, a second service or a hand-run DELETE in a console. Application-level checks protect exactly the writes that go through the application, which is never all of them.

A foreign key also buys two things teams rarely count:

• Referential actions. ON DELETE CASCADE, SET NULL and RESTRICT decide what a delete does to child rows, inside the same transaction. Rebuilding that in application code means reimplementing it in every place that deletes, and a cascade is already easy to miss even when it is declared.
• Discoverability. Diagram tools, BI tools, query builders and the next engineer all read the relationship graph from the catalog. Snowflake Labs, in the README of its dbt_constraints package, gives this as the primary reason to add constraints at all: tools “including DBeaver and Oracle SQL Developer Data Modeler can correctly reverse-engineer data model diagrams if there are primary keys, unique keys, and foreign keys on tables”, and “most BI tools will also add joins automatically.”

The cost is a parent lookup on each child insert or update, and a child lookup on each parent delete, which is why the referencing column should carry an index. For most transactional schemas that is cheap next to a table full of orders pointing at customers that no longer exist.

When is it right to skip foreign keys?

Three situations come up again and again, and in each of them the reason is a documented property of the software involved, not a preference.

The engine does not enforce them. Snowflake’s table design documentation is explicit: on standard tables, “referential integrity constraints, as defined by primary-key/foreign-key relationships, are informational; they are not enforced.” Only NOT NULL and CHECK are. Warehouses built with CREATE TABLE AS SELECT get no constraints at all, because that statement copies columns, not keys.

Your online schema change tool refuses tables that have them. GitHub’s gh-ost states it in its requirements and limitations: “Foreign key constraints are not supported.” Vitess reached the same conclusion for its online DDL, in a June 2021 post explaining why foreign keys are not supported: it is “unfortunately not feasible given the design of online schema change tools”, because a table copy that swaps in at the end leaves child constraints pointing at the old table. A team that depends on non-blocking migrations of large MySQL tables is choosing between those tools and declared keys.

Related rows live in different places. Once parent and child rows can sit on different shards or in different databases, no single engine can check the reference, and it moves to the application whether you like it or not.

What none of these three changes is the relationship itself. The warehouse’s fact table still joins to its dimensions on customer_key; the MySQL schema migrated by gh-ost still has orders.customer_id. Skipping the constraint turns a fact the catalog stores into a fact that lives only in column names, in application code and in people’s heads.

What does a schema without foreign keys cost the people who read it?

It opens as boxes. Every reverse-engineering tool that builds relationships from the catalog draws a schema without declared keys as tables with nothing between them, which is exactly the forum complaint above, and exactly the schema a new engineer is handed when they need a map most. On a large legacy database that is the moment the instinct to diagram everything runs straight into a picture with no structure to read.

The usual fixes are both poor. Adding the keys “for documentation” on a database that will not enforce them works in a warehouse and is not an option under gh-ost, where the constraint itself is what the tool refuses. Drawing the lines by hand in a diagram tool produces a second model that drifts the next time the schema changes, and on a schema with hundreds of undeclared references it is not a job anyone finishes.

The same goes for everything built on the relationship graph. A context map derived from your foreign keys cannot show a dependency that no key declares, and a check for a cycle of references that blocks the first insert has no references to walk.

How do you add foreign keys to a schema that never had them?

Carefully, because the data has had years to drift. ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY scans every existing row, and one child row pointing at a deleted parent fails the whole statement.

PostgreSQL gives you a way to split the risk. According to the ALTER TABLE documentation, adding the key with NOT VALID skips the scan of existing rows while “the constraint will still be applied against subsequent inserts or updates”, so new orphans stop immediately. A later VALIDATE CONSTRAINT does the scan while taking only a SHARE UPDATE EXCLUSIVE lock on the table being altered and a ROW SHARE lock on the referenced one, so ordinary reads and writes carry on.

Neither step tells you how many orphans are waiting. That is a query you write per relationship, LEFT JOIN against the parent and count the misses, and it is easy to skip on the one table that matters.

How Schemity documents the relations you cannot declare

Schemity treats a relationship the database does not declare as a first-class part of the diagram rather than as a missing line. A virtual relation is a foreign key that exists only in the diagram: drawn dashed, saved in the diagram’s JSON, and never written to the database by a migration, a SQL export or a DBML export. You draw it the same way as any relationship and switch the dialog to Virtual relation, which asks which columns already carry the dependency instead of creating a new one. It offers no ON DELETE or ON UPDATE, because nothing will perform them, and it survives a re-sync against the live database, so the documentation does not disappear the next time the schema is refreshed.

For a schema with hundreds of undeclared references, drawing is still not a workflow, so Schemity also infers them. When a PostgreSQL, MySQL, MariaDB, SQL Server or SQLite database is reverse engineered into an ERD, column names are split into words and matched against entity names: orders.customer_id reaches customers, userProfileId reaches user_profiles, and created_by_user_id falls back until it reaches users. The matcher is deliberately conservative. It skips a column already covered by a declared key, a pair whose types disagree, and any name two tables could claim, and it does not guess on warehouse suffixes like _key, because api_key has the same shape. A notification reports how many relations were inferred, and deleting one records the decision in the diagram file so it does not return. The full rules are in Virtual & Inferred Relations.

Schema lint, which checks the open diagram against seventeen rules, keeps the two ideas apart. Junction table detection counts virtual keys, so link tables are recognized on a schema where every relation is virtual, while the not-null cycle rule ignores them, because a cycle only blocks inserts if the database checks it. The Context Map counts both kinds in its arrows and tags each undeclared row, since a dependency between two modules is real whether or not a constraint enforces it.

When the time comes to declare the keys for real, the orphan count is the question to answer first. Paste the migration, hand-written or generated, into Analyse a migration file, and impact analysis reports each ADD CONSTRAINT ... FOREIGN KEY under statements that may fail on existing data, with Count exactly running the read-only orphan count for that relationship. Nothing in the file is executed, so the answer arrives before the ALTER does.

Declaring the keys you can, documenting the ones you cannot

The decision is less binary than the debate makes it sound. Declare foreign keys on every transactional schema that can carry them; they cost an index and a lookup, and they catch the write paths your application never sees. Where the engine, the migration tooling or the data layout rules them out, or where a polymorphic association points one column at several tables, keep the relationships anyway, as documentation that tools and people can read rather than as knowledge in whoever wrote the join.

That is also what makes the schema teachable. A diagram of real references, declared or not, is what turns a multi-tenant data model into something a new engineer can follow in an afternoon, and it is the same argument as keeping a list of ids out of an array column: a relationship the schema cannot show is a relationship the next person has to rediscover.