Foreign Keys & Referential Integrity
debt(d5/e5/b7/t5)
Closest to 'specialist tool catches' (d5). Missing FK constraints are detectable via mysql-workbench, pgadmin, or laravel-migrations as listed in detection_hints.tools. These are specialist database tools rather than default linters — orphaned records and missing constraints require intentional schema inspection or migration review to identify.
Closest to 'touches multiple files / significant refactor' (e5). The quick_fix suggests adding FK constraints, but retrofitting them to an existing database with orphaned data requires cleaning up invalid records first, potentially modifying multiple migrations, adding indexes on FK columns, and testing cascade behaviors across the application — this touches multiple files and requires careful coordination.
Closest to 'strong gravitational pull' (b7). Per applies_to, this affects all PHP contexts (web, cli, queue-worker). The absence of FK constraints is a foundational database design choice that shapes how all data operations work — every query, every delete, every migration must account for whether integrity is enforced at the database level. This is load-bearing across the entire system and difficult to change once established.
Closest to 'notable trap / documented gotcha' (t5). The misconception explicitly states developers believe 'ORMs handle referential integrity so FK constraints are redundant.' This is a well-documented gotcha that most experienced developers eventually learn, but it contradicts the intuition that if your ORM handles relationships, the database doesn't need to enforce them. The common_mistakes around CASCADE defaults and missing indexes add additional traps.
Also Known As
TL;DR
Explanation
Foreign keys enforce that every child row has a corresponding parent row. ON DELETE CASCADE removes children when the parent is deleted. ON DELETE RESTRICT (default in PostgreSQL) blocks deletion if children exist. ON DELETE SET NULL nullifies the FK column. Foreign keys also create implicit indexes in MySQL but not PostgreSQL — you must add them explicitly. Many applications disable FK constraints for performance and rely on application-level validation, which always drifts.
Diagram
flowchart TD
USERS[(users<br/>id PK)]
ORDERS[(orders<br/>id PK<br/>user_id FK)]
ITEMS[(order_items<br/>id PK<br/>order_id FK)]
USERS -->|ON DELETE RESTRICT| ORDERS
ORDERS -->|ON DELETE CASCADE| ITEMS
BAD[DELETE user 42] -->|blocked| USERS
note1[FK prevents orphaned<br/>orders referencing<br/>deleted user]
style USERS fill:#1f6feb,color:#fff
style ORDERS fill:#238636,color:#fff
style ITEMS fill:#238636,color:#fff
Common Misconception
Why It Matters
Common Mistakes
- Not adding an index on the FK column in PostgreSQL — joins and cascade operations do full table scans without it.
- Using ON DELETE CASCADE everywhere without considering whether child records should survive parent deletion.
- Disabling FK checks during bulk imports and forgetting to re-enable them — orphaned rows persist permanently.
- Not specifying ON DELETE behaviour — defaults differ between MySQL (RESTRICT) and other databases.
Code Examples
-- No FK constraint — orphaned rows accumulate silently:
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT -- No FK: user deleted, orders remain with dangling user_id
);
-- DELETE FROM users WHERE id = 42;
-- orders still has rows with user_id = 42 — silent data corruption-- FK with explicit delete behaviour:
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT NOT NULL
REFERENCES users(id)
ON DELETE RESTRICT -- Block user deletion if orders exist
);
-- PostgreSQL: also add index
CREATE INDEX idx_orders_user_id ON orders(user_id);