Ask a longtime MySQL operator and a longtime PostgreSQL operator about foreign keys and you will hear two different worldviews. The MySQL answer is often "we enforce that in the application"; the PostgreSQL answer is often "the database is the last line of defense." Neither position is stupid, and both are products of history. But when you migrate a schema from one culture to the other, the difference stops being philosophical: you have years of unconstrained data meeting an engine that takes constraints seriously.
This article is about making that meeting go well: where the FK-less habit came from, what PostgreSQL actually enforces, and the specific mechanics, NOT VALID, deferred constraints, deliberate ON DELETE choices, that let you add integrity to imperfect data without a big-bang outage.
Where the two cultures came from
MySQL's original default engine, MyISAM, did not support foreign keys at all; it parsed the syntax and silently ignored it, which is arguably worse than rejecting it. A generation of applications, including some of the largest web platforms ever built, grew up enforcing referential integrity in application code because the storage engine gave them no choice. Even after InnoDB became the default in 5.5, the habit persisted, reinforced by real operational arguments: FK checks add write overhead, complicate online schema-change tooling, and get in the way of sharding.
PostgreSQL enforced foreign keys from early on, and its community norms formed around declaring them. The practical result is that a typical mature MySQL schema arriving at a migration has somewhere between "some" and "no" foreign keys, and the data reflects it: orphaned rows, dangling references, and delete logic scattered across application services.
What each engine actually enforces today
To be fair to modern MySQL: InnoDB foreign keys are real. They enforce referential integrity, support the standard ON DELETE and ON UPDATE actions, and require an index on the referencing column, creating one automatically if needed. The remaining gaps are specific: partitioned InnoDB tables cannot have foreign keys in either direction, constraints cannot be deferred, and the global FOREIGN_KEY_CHECKS switch makes it culturally easy to bypass checking during imports and forget what that implies.
PostgreSQL 16 and 17 enforce foreign keys everywhere, including on partitioned tables (a partitioned table can both reference and be referenced). There is no casual session-level off switch; the closest equivalent, session_replication_role, is a replication tool, not an import convenience, and using it that way trades away exactly the guarantee you migrated for. What PostgreSQL gives you instead of an off switch is finer-grained control: constraints that can be added without immediate validation, and constraints whose checking can be deferred to commit.
Adding foreign keys to data that was never checked
The naive approach, declare all the FKs in the new schema and load the data, fails on the first orphan row, and a decade of FK-less operation guarantees orphans. The honest sequence is: measure the dirt, decide what it means, then constrain.
Measuring is a set of anti-join queries, one per intended constraint:
-- Orphaned order items: parent order no longer exists
SELECT count(*) AS orphans
FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;Each nonzero count forces a product decision, not just a technical one: delete the orphans, reparent them to a sentinel row, or set the reference NULL and make the column nullable. Do this analysis on the MySQL side before cutover if you can; it belongs on the migration review checklist precisely because it takes calendar time to resolve.
For large tables already live on PostgreSQL, add the constraint in two steps so you never hold a long lock while the whole table is checked:
ALTER TABLE order_items
ADD CONSTRAINT order_items_order_id_fkey
FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;
-- Later, at leisure; checks existing rows without blocking writes:
ALTER TABLE order_items VALIDATE CONSTRAINT order_items_order_id_fkey;NOT VALID means new and updated rows are checked immediately, so the dirt stops growing, while existing rows wait for VALIDATE, which takes only a lock that permits normal reads and writes. This two-step is the single most useful FK tool PostgreSQL gives a migrating team: constraints can arrive table by table, on a schedule, each validation run sized to a quiet window, with the schema honest about its state the whole time.
Deferred constraints for awkward write orders
Some application code cannot satisfy constraints statement-by-statement: circular references between two rows inserted in the same transaction, bulk swaps of identifiers, or ORMs that flush in an order you do not control. MySQL has no answer for this except disabling checks. PostgreSQL's answer is deferrable constraints: declare the FK as DEFERRABLE INITIALLY DEFERRED (or INITIALLY IMMEDIATE and defer it per-transaction with SET CONSTRAINTS), and the check runs at commit instead of per statement. Integrity is still guaranteed, just at transaction granularity, which is where it logically belongs anyway.
The circular case makes it concrete: a teams table whose lead_user_id references users, and a users table whose team_id references teams. Inserting a new team with its first lead is impossible statement-by-statement in either order. With the constraints declared DEFERRABLE INITIALLY DEFERRED, both inserts run inside one transaction and the checks pass together at commit, no NULL-then-update dance and no disabled checking. This is the pattern that turns "we had to drop the FK because the app writes in the wrong order" into a solved problem rather than a schema concession.
Two cautions from operating this. Deferring widens the window in which a transaction holds its work before failing, so bulk loads that defer everything can do a lot of doomed work before commit rejects them. And deferrable constraints carry a small bookkeeping cost, so reserve the property for the constraints that need it rather than declaring everything deferrable by default.
Choosing ON DELETE deliberately
Migrations tend to copy delete behavior thoughtlessly, and this is the moment to choose. Remember that on the MySQL side much of this logic lived in application services, so the migration question is not "what did the old FKs say" but "what did the delete code actually do," and the two frequently disagree. NO ACTION (the default) and RESTRICT refuse the delete while children exist, which is the right default for anything involving money or audit history; the difference is that NO ACTION can be deferred while RESTRICT checks immediately. CASCADE removes children with the parent, correct for genuine ownership (order items under an order) and dangerous for anything shared, since one delete can silently fan out across millions of rows in a single transaction, with the lock footprint that implies. SET NULL detaches children and requires nullable columns and code that expects the NULL; PostgreSQL 15+ can even restrict which columns get nulled in composite keys. SET DEFAULT is niche but occasionally exactly right with a sentinel row.
One PostgreSQL-specific trap deserves emphasis: unlike InnoDB, PostgreSQL does not automatically index the referencing column. Every FK you add needs a matching index on the child side, or deletes on the parent will sequentially scan the child table per row, which is how a routine cleanup job becomes a lock pileup. Auditing for unindexed FK columns should be part of standing schema review on the PostgreSQL side, not a one-time task.
How MonPG helps after you land on PostgreSQL
Foreign keys change your workload's failure modes. An unindexed FK column shows up as sequential scans and slow deletes. A too-eager CASCADE shows up as long transactions and lock waits fanning out from a single parent delete. A VALIDATE CONSTRAINT run on a big table is a burst of read I/O you want to schedule with evidence. Constraint violations from application paths that never expected enforcement show up as error spikes tied to specific query families.
MonPG watches exactly these signals on PostgreSQL: lock chains and blocking sessions, long-running transactions, per-query-family latency and error trends, and the scan patterns that reveal a missing child-side index before the cleanup job finds it for you. It monitors PostgreSQL only, which is precisely the side of the migration where your new constraints live. The PostgreSQL monitoring guide describes the baseline worth having in place before you run your first VALIDATE on production.