Foreign Key

Explanation

A foreign key constraint says: every value in this column must either be NULL or match an existing primary key value in the referenced table. Without this constraint, you can insert an order with user_id = 999 even if user 999 doesn't exist — an orphaned record that causes application errors when you try to load the user for that order. ON DELETE and ON UPDATE actions define what happens when the referenced row changes: RESTRICT/NO ACTION (default in most databases — prevent deletion if referenced rows exist), CASCADE (automatically delete all referencing rows when the referenced row is deleted — use carefully!), SET NULL (set the FK column to NULL when the referenced row is deleted — useful for optional relationships), and SET DEFAULT. Many-to-one (most common): many orders belong to one user (orders.user_id → users.id). One-to-one: one user has one profile (profile.user_id → users.id, with UNIQUE constraint on user_id). Many-to-many: implemented via a junction table with two foreign keys (user_roles with user_id and role_id, each referencing their respective tables). Performance note: foreign key columns should always be indexed. When you JOIN orders ON orders.user_id = users.id, PostgreSQL uses the index on orders.user_id to find matching rows. Without it, every JOIN requires a full scan of orders. Many databases (MySQL) create the index automatically; PostgreSQL does not — you must add it explicitly.

Code Example

-- Foreign key relationships and actions

-- One-to-many: posts belong to users
CREATE TABLE posts (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  -- When a user is deleted, all their posts are automatically deleted
  title   TEXT NOT NULL,
  body    TEXT
);
CREATE INDEX idx_posts_user_id ON posts(user_id);  -- required for JOIN performance

-- Optional relationship: SET NULL when referenced row is deleted
CREATE TABLE comments (
  id      SERIAL PRIMARY KEY,
  post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL, -- keep comment, remove user ref
  body    TEXT NOT NULL
);

-- Many-to-many: users have many roles, roles have many users
CREATE TABLE roles (id SERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL);

CREATE TABLE user_roles (
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, role_id),
  assigned_at TIMESTAMP DEFAULT NOW()
);

-- Query: users with their roles
SELECT u.name, r.name AS role
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.id = 42;

Why It Matters for Engineers

Foreign keys are the database's mechanism for maintaining data integrity — the guarantee that your data relationships are consistent. Without FK constraints, orphaned records accumulate silently and cause mysterious application errors ("user not found" for an existing order). With FK constraints, the database refuses invalid data at the source. ON DELETE CASCADE is particularly important to understand: cascading deletes can accidentally delete large amounts of related data. Deleting a user with CASCADE on all their data is sometimes correct (a user requests account deletion) and sometimes catastrophic (a bug deletes a production admin user, cascading to all their content).