The Point

Foreign keys and JOINs solve two different problems: FK ensures data integrity at the write layer, while JOIN associates data at the read layer. Not having a FK does not prevent JOINs, but you lose the database-level safety net.

Explanation

Two core functions of a Foreign Key

Using shipping_fees (FK) -> shipping_classes (PK) as an example:

1. Referential integrity on insert: shipping_fees cannot contain a shipping_class_id that does not exist in shipping_classes
2. Cascading protection on delete: as long as any shipping_fees row references a shipping_class, that shipping_class row cannot be deleted

1-- With FK, the database blocks both of these:
2INSERT INTO shipping_fees (shipping_class_id, ...) VALUES (999, ...);
3-- ERROR: Cannot add or update a child row: foreign key constraint fails
4
5DELETE FROM shipping_classes WHERE id = 1;
6-- ERROR: Cannot delete or update a parent row: foreign key constraint fails

JOIN does something different

JOIN only associates data from two tables at query time. It does not care whether the data is valid:

1SELECT sf.*, sc.name
2FROM shipping_fees sf
3JOIN shipping_classes sc ON sf.shipping_class_id = sc.id;

You can JOIN without a FK, but if there are orphan records, the JOIN silently excludes them without raising an error.

Why do DBRE teams dislike FK?

FK causes trouble at the DB operations layer:

• Emergency data fixes via DML (direct INSERT / DELETE) can be blocked by FK constraints
• Large data migrations require temporarily disabling FK checks
• FK behavior gets more complex in replication setups

The compromise

Keep the JOIN design (no FK), but the DBRE team uses DML validation at the operations layer to ensure data correctness – essentially replacing FK’s two protections with a manual process.

Knowledge Sugar

FK vs Application-level validation vs DML validation

The nature of this tradeoff

FK means “let the database worry about data correctness.” No FK means “we worry about it ourselves.” The former is safer but sacrifices operational flexibility; the latter is more flexible but shifts responsibility to the application and operations layers.

For high-traffic systems or those with urgent on-call needs, DBRE teams tend to prefer the latter – they do not want to be stuck on a constraint during a critical moment.