Foreign Key Constraints
Database-enforced rules that guarantee a value in one table's column must reference an existing row in another table, preventing orphaned records.
Description
Foreign key constraints enforce referential integrity at the database level. When you declare a column as REFERENCES another_table(id), the database guarantees that: (1) you cannot INSERT a row with a foreign key value that does not exist in the referenced table, (2) you cannot DELETE a referenced row if other rows still point to it (unless cascading is configured), and (3) you cannot UPDATE the referenced key to a value that would break existing references. This prevents orphaned records -- orders pointing to non-existent customers, for example.
Foreign keys also serve as living documentation of your data model's relationships. They make the schema self-describing: anyone examining the table definitions can understand how entities relate. Many tools (ER diagram generators, ORM schema introspection, database IDEs) use foreign keys to visualize and navigate relationships.
There are legitimate reasons to omit foreign keys: extreme write throughput requirements (FK checks add overhead to every INSERT/UPDATE/DELETE), sharded databases (cross-shard FKs are impossible), and polyglot persistence (references between different database systems). However, for single-database applications with moderate write volumes, the data integrity guarantee is almost always worth the small performance cost. When you must omit FKs, enforce referential integrity in the application layer and run periodic consistency checks to detect orphaned records.
Prompt Snippet
Define foreign key constraints on all relationship columns with explicit ON DELETE behavior: use CASCADE for dependent children (order_items -> orders), RESTRICT for important references that should block deletion (orders -> customers), and SET NULL for optional associations. Name constraints explicitly (CONSTRAINT fk_orders_customer FOREIGN KEY ...) for readable migration output. Create indexes on all foreign key columns -- PostgreSQL does not auto-index FKs, and missing indexes cause sequential scans during JOIN operations and CASCADE deletes. Run periodic orphan detection queries in CI to catch any application-layer FK violations.
Tags
Related Terms
Cascading Deletes
Automatically deleting child rows when a parent row is deleted, enforced by foreign key constraints with ON DELETE CASCADE.
Database Normalization (1NF-3NF)
The process of organizing database columns and tables to reduce data redundancy and improve data integrity through progressive normal forms.
UUID vs Auto-Increment PKs
Choosing between auto-incrementing integers and UUIDs (or similar) as primary keys, balancing index performance, security, and distributed generation.