LLM Best Practices

Foreign key

2 min read

View markdown source

Overview

This page is the atomic definition. Schema design patterns live at postgres and prisma.

Definition

A foreign key (FK) is a column or composite column set in a child table that references the primary key (or a unique key) of a parent table. The database enforces referential integrity: an INSERT into the child is rejected if the referenced parent row does not exist; a DELETE from the parent is rejected (or cascaded) if child rows reference it. PostgreSQL checks FK constraints within the same transaction, which prevents orphaned rows even under concurrent writes. Performance note: every FK column should also have an index on the child side because Postgres does not create one automatically, and the constraint check runs a lookup against the parent table. ON DELETE CASCADE automatically removes child rows when the parent is deleted; ON DELETE SET NULL nullifies the FK column; ON DELETE RESTRICT (default) blocks the delete.

When it applies

Define foreign keys for every parent-child relationship where orphaned child rows would cause application errors. Skip FKs on very high-write tables where the constraint check becomes a bottleneck and the application enforces the relationship in code.

Example

CREATE TABLE orders (
  id       bigint PRIMARY KEY,
  user_id  bigint NOT NULL REFERENCES users(id) ON DELETE RESTRICT
);
 
-- Index the FK column to avoid full-table scan on user deletes.
CREATE INDEX orders_user_id_idx ON orders (user_id);
  • postgres - FK syntax, deferrable constraints, and cascade options.
  • prisma - Prisma generates FK constraints from @relation annotations.
  • secondary-index - always index FK columns on the child side.
  • deadlock - FK constraint checks contribute to lock contention.
  • acid - FK checks run within the transaction for consistency.

Citing this term

See Foreign key (llmbestpractices.com/glossary/foreign-key).

Graph View

Backlinks