Foreign Keys and Relationships: Linking Tables

In Schema Design, you used REFERENCES to connect tables. That one keyword — REFERENCES orders(id) — is a foreign key constraint: a rule that says "the value in this column must exist as a primary key in that other table."

But foreign keys have more to them than a simple reference. What happens when you delete a user who has orders? What happens when you update a product ID that order items reference? These are the questions this article answers — and getting them right is the difference between a schema that protects your data and one that lets it quietly fall apart.

---

Quick Reference

Define a foreign key:

-- Column-level
user_id  INTEGER NOT NULL REFERENCES users(id)

-- Table-level (with explicit name and action)
CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE RESTRICT

Delete behaviors:

Action	Effect when parent row is deleted
RESTRICT (default)	Blocks deletion if child rows exist
NO ACTION	Like RESTRICT, checked at end of transaction
CASCADE	Automatically deletes child rows
SET NULL	Sets the FK column to NULL
SET DEFAULT	Sets the FK column to its DEFAULT value

Gotchas:

• ⚠️ Foreign key columns should almost always have an index — without one, every parent update/delete requires a full scan of the child table
• ⚠️ CASCADE deletes can propagate further than expected if multiple levels of foreign keys use it
• ⚠️ SET NULL requires the FK column to be nullable — can't use it with NOT NULL

---

Version Information

Tested with:

• PostgreSQL: 15.x, 16.x
• Node.js: v18.x, v20.x, v22.x

---

What You Need to Know First

Required reading:

• Schema Design: Tables, Types, and Constraints — PRIMARY KEY, NOT NULL, REFERENCES
• SQL Joins: Combining Tables — the join queries in this article reference foreign key relationships

What We'll Cover in This Article

By the end of this guide, you'll understand:

• What referential integrity means and why the database enforces it
• How REFERENCES creates a foreign key constraint
• The difference between ON DELETE RESTRICT, CASCADE, SET NULL, and SET DEFAULT
• Why foreign key columns need indexes
• How to design one-to-many relationships
• How to design many-to-many relationships using junction tables
• How to design one-to-one relationships

What We'll Explain Along the Way

We'll introduce these concepts with full explanations:

• What "deferrable" constraints are (briefly)
• Why NO ACTION and RESTRICT are almost the same — but not quite
• The index PostgreSQL creates automatically for primary keys (and the one it doesn't create for foreign keys)

---

Part 1: What Referential Integrity Means

Referential integrity means that every foreign key value in a child table must correspond to an existing primary key in the parent table. There are no dangling references — no order that points to a user who doesn't exist, no order item that references a deleted product.

Without enforcement, data becomes inconsistent in subtle ways. Imagine deleting a user and forgetting to delete their orders. Now you have orders in the database with user_id = 3, but no user with id = 3. A query that joins orders to users silently drops those orders. An application that tries to display the order's customer name gets NULL. These bugs are hard to find and even harder to fix once the data is in production.

Foreign key constraints make the database refuse operations that would create inconsistency — before the inconsistency happens.

---

Part 2: The REFERENCES Syntax

You've already seen the basic form:

user_id  INTEGER NOT NULL REFERENCES users(id)

This says: "The value of user_id must exist as a value of id in the users table."

The full syntax with all options:

-- Column-level (applies to one column)
column_name  data_type  REFERENCES parent_table(parent_column)
  ON DELETE action
  ON UPDATE action

-- Table-level (supports composite foreign keys and explicit naming)
CONSTRAINT constraint_name
  FOREIGN KEY (column1, column2)
  REFERENCES parent_table(parent_column1, parent_column2)
  ON DELETE action
  ON UPDATE action

If you omit ON DELETE and ON UPDATE, the default action is NO ACTION — which behaves like RESTRICT (blocks the operation if it would create an orphan).

Naming Your Constraints

PostgreSQL auto-generates constraint names (orders_user_id_fkey), but explicit names make error messages clearer and make it easier to drop or modify specific constraints later:

CREATE TABLE orders (
  id       SERIAL PRIMARY KEY,
  user_id  INTEGER NOT NULL,

  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE RESTRICT
);

---

Part 3: What Happens on Delete? The Five Actions

This is the core design decision for every foreign key. What should happen to child rows when a parent row is deleted?

RESTRICT — Block the Delete (Default)

The parent row cannot be deleted while child rows reference it. The delete is blocked with an error.

user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT

-- ❌ Blocked: user 1 has orders
DELETE FROM users WHERE id = 1;
-- ERROR: update or delete on table "users" violates foreign key constraint
-- "fk_orders_user" on table "orders"
-- DETAIL: Key (id)=(1) is still referenced from table "orders".

Use RESTRICT when: deleting the parent without handling children first is always a mistake. This is the right choice for orders.user_id — you shouldn't delete a user with existing orders without explicitly deciding what to do with those orders first.

CASCADE — Automatically Delete Children

When a parent row is deleted, all child rows that reference it are automatically deleted too.

order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE

-- Deleting order 1 automatically deletes its order_items
DELETE FROM orders WHERE id = 1;
-- DELETE 1
-- (order_items for order 1 are also deleted automatically)

Use CASCADE when: child rows have no meaning without the parent. order_items without their parent order is meaningless data. CASCADE is the right choice there.

Be careful with cascade chains: if orders cascades to order_items, and order_items cascades to something else, deleting a user (if user deletion cascaded to orders) would trigger a chain that might delete more data than expected.

Here's the cascade chain for ShopFlow, traced explicitly:

Delete user
  → CASCADE to orders (if configured)
    → CASCADE to order_items (if configured)

We'll not cascade from users to orders in ShopFlow — deleting a user's account shouldn't silently erase order history (which may be needed for accounting). RESTRICT on orders.user_id forces explicit handling.

SET NULL — Clear the Reference

When a parent row is deleted, the FK column in child rows is set to NULL.

-- requires the column to be nullable (no NOT NULL)
category_id  INTEGER REFERENCES categories(id) ON DELETE SET NULL

Use SET NULL when: the child row remains valid without the parent. An article that loses its category can still exist — it just becomes uncategorized. A product that loses its subcategory still exists as a product.

This is incompatible with NOT NULL — you can't set a required column to NULL.

SET DEFAULT — Restore to Default

When a parent row is deleted, the FK column is set to its DEFAULT value.

assigned_to  INTEGER DEFAULT 1 REFERENCES users(id) ON DELETE SET DEFAULT

Use SET DEFAULT when: there's a sensible fallback parent (like an "unassigned" or "general" category). Rare in practice — most schemas use RESTRICT or CASCADE.

NO ACTION — Like RESTRICT, But Deferred

NO ACTION is technically different from RESTRICT in how it interacts with deferrable constraints — it allows the constraint to be checked at the end of a transaction rather than immediately. In practice, for non-deferrable constraints (the default), NO ACTION and RESTRICT behave identically.

You'll see NO ACTION in database tooling output — it's the default when no ON DELETE is specified.

---

Part 4: Designing the ShopFlow Relationships

Let's apply these concepts to build the ShopFlow foreign keys deliberately.

orders.user_id → users.id

-- Behavior: block deletion of users with existing orders
-- Reason: order history is important; don't silently delete it
CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE RESTRICT
  ON UPDATE CASCADE  -- if a user's id changes (rare), propagate it

order_items.order_id → orders.id

-- Behavior: cascade deletion of order items when an order is deleted
-- Reason: order items have no meaning without their parent order
CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id) REFERENCES orders(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE

order_items.product_id → products.id

-- Behavior: restrict deletion of products with order history
-- Reason: you need historical order items even if a product is discontinued
-- (consider "soft deletes" with an is_active column instead)
CONSTRAINT fk_order_items_product
  FOREIGN KEY (product_id) REFERENCES products(id)
  ON DELETE RESTRICT
  ON UPDATE CASCADE

Full order_items table with all FK constraints:

CREATE TABLE order_items (
  id                 SERIAL PRIMARY KEY,
  order_id           INTEGER NOT NULL,
  product_id         INTEGER NOT NULL,
  quantity           INTEGER NOT NULL CHECK (quantity > 0),
  price_at_purchase  NUMERIC(10, 2) NOT NULL CHECK (price_at_purchase >= 0),

  UNIQUE (order_id, product_id),

  CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_order_items_product
    FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

---

Part 5: Relationship Patterns

One-to-Many

The most common relationship. One parent row has many child rows, each with a NOT NULL foreign key pointing back to the parent.

users (1) ──────────── orders (many)
  id  ◄─────────────── user_id

-- users: parent table (no FK)
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

-- orders: child table (FK to users)
CREATE TABLE orders (
  id       SERIAL PRIMARY KEY,
  user_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  status   TEXT NOT NULL DEFAULT 'pending'
);

Many-to-Many via Junction Table

When many rows in table A relate to many rows in table B, you need a junction table (also called a join table or associative table). It has two foreign keys — one to each side of the relationship — and the combination of the two is unique.

orders (many) ──── order_items (junction) ──── products (many)
  id ◄──────────── order_id   product_id ──────────────► id

CREATE TABLE order_items (
  id                 SERIAL PRIMARY KEY,
  order_id           INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id         INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity           INTEGER NOT NULL CHECK (quantity > 0),
  price_at_purchase  NUMERIC(10, 2) NOT NULL,
  UNIQUE (order_id, product_id)
);

The junction table often carries additional data about the relationship — quantity and price_at_purchase here. This data belongs on the relationship, not on either parent table.

Using a composite primary key instead of a surrogate id:

Some schemas use PRIMARY KEY (order_id, product_id) instead of a separate id SERIAL column. Both approaches are valid:

• Surrogate id — simpler when the junction table is referenced by yet another table
• Composite PK — slightly more explicit, enforces uniqueness at the PK level rather than via a separate UNIQUE constraint

One-to-One

Each row in table A relates to exactly one row in table B, and vice versa. Implemented with a UNIQUE foreign key.

-- users: main table
-- user_profiles: extended data, separated for access pattern reasons
CREATE TABLE user_profiles (
  user_id     INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  bio         TEXT,
  avatar_url  TEXT,
  location    TEXT
);

Making user_id the PRIMARY KEY (instead of a separate id column) enforces the one-to-one: each user can have at most one profile, and each profile belongs to exactly one user.

When to use one-to-one:

• Splitting a table that's grown too wide (separating frequently-accessed columns from rarely-accessed ones)
• Separating data with different security requirements (user credentials vs user preferences)
• Optional "extension" data (not every user has a profile)

---

Part 6: Foreign Key Indexes — The Performance You Need to Add

PostgreSQL automatically creates an index for PRIMARY KEY and UNIQUE columns. It does not automatically create an index for foreign key columns.

This matters for performance. Every time you delete or update a parent row, PostgreSQL must check that no child rows reference it. Without an index on the foreign key column, this check requires a full scan of the child table — which becomes expensive as the table grows.

-- These indexes should be created for every FK column
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Beyond constraint checks, these indexes also speed up JOIN queries — which almost always join on foreign key columns. The indexes are nearly always worth it.

Rule: Create an index on every foreign key column, unless you have a specific reason not to.

We'll go deeper into index types and query planning in a future article on Indexes.

---

Common Misconceptions

❌ Misconception: CASCADE on user deletion is convenient and safe

Reality: Cascading from users to orders means deleting a user account silently erases all order history — which may be needed for accounting, fraud investigation, or legal compliance.

Better approach: Use RESTRICT on orders.user_id. When a user wants to delete their account, explicitly decide what happens to their orders: anonymize the user data (set user_id to a placeholder, keep the orders), archive the orders, or handle it in application code with explicit steps.

❌ Misconception: Foreign key constraints slow down all queries

Reality: Foreign keys add a one-row lookup on each INSERT/UPDATE/DELETE that touches the FK column — negligible with an index on the referenced primary key (which always exists). The index PostgreSQL automatically creates on primary keys makes FK checks fast.

The actual performance concern is the missing index on the FK column in the child table — not the FK constraint itself.

❌ Misconception: You can safely add a FK constraint to an existing table without checking data first

Reality: Adding a foreign key to a table with existing data that violates the constraint will fail:

-- ❌ Fails if any orders.user_id values have no matching users.id
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id);
-- ERROR: insert or update on table "orders" violates foreign key constraint

Fix: Clean the data first, or use NOT VALID to skip checking existing rows and then validate separately:

-- Add constraint without checking existing rows
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

-- Validate existing rows separately (doesn't lock the table as aggressively)
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

---

Troubleshooting Common Issues

Problem: Can't delete a parent row — FK violation error

Symptoms: DELETE FROM users WHERE id = 1 fails with a foreign key violation.

Cause: Child rows in another table reference this row, and the FK is configured with RESTRICT or NO ACTION.

Solutions:

-- Option 1: Delete children first, then parent
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;

-- Option 2: If using CASCADE, just delete the parent
-- (children are automatically removed)

-- Option 3: Use a transaction to do it safely
BEGIN;
  DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE user_id = 1);
  DELETE FROM orders WHERE user_id = 1;
  DELETE FROM users WHERE id = 1;
COMMIT;

Problem: FK constraint check is slow on large tables

Symptoms: INSERT or UPDATE on a child table is noticeably slow when the parent table is large.

Cause: No index on the parent table's referenced column — but this is always the primary key, which is always indexed. More likely: no index on the child table's FK column, making parent-side delete checks slow.

Solution:

-- Add index on the FK column in the child table
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Verify it's being used
EXPLAIN ANALYZE DELETE FROM users WHERE id = 1;

---

Check Your Understanding

Quick Quiz

1. A product is referenced by 50 order_items. You try to DELETE FROM products WHERE id = 5. The FK is ON DELETE RESTRICT. What happens?

   Show Answer

   The delete is blocked with an error. PostgreSQL detects that order_items rows reference products.id = 5 and refuses to delete the product while those references exist. To delete the product, you'd first need to delete or reassign the referencing order_items rows (or change the FK to CASCADE or SET NULL).

2. When would you use ON DELETE SET NULL instead of ON DELETE CASCADE?

   Show Answer

   Use SET NULL when the child row remains valid and meaningful without the parent. For example: an articles table with an optional category_id — if a category is deleted, the article still exists, just uncategorized (category_id becomes NULL). Use CASCADE when the child row has no meaning without the parent — like order_items without their parent order.

3. Why does PostgreSQL not automatically create an index on foreign key columns?

   Show Answer

   PostgreSQL creates indexes automatically for PRIMARY KEY and UNIQUE constraints because they always require uniqueness checking on every write. Foreign key indexes only help performance — the constraint can be enforced by a (slow) table scan. PostgreSQL leaves the decision to the developer because there are cases where the index isn't worth it (very small tables, write-heavy tables where the index overhead matters). In practice, you should almost always add the index manually.

Hands-On Challenge

Task: Add a categories table to ShopFlow, where each product belongs to a category. Design the relationship so that:

1. A category can have many products
2. Deleting a category sets the product's category_id to NULL (products still exist, just uncategorized)
3. Products can be uncategorized (no category required)
4. Add the appropriate index

Show Solution

-- Step 1: Create the categories table
CREATE TABLE categories (
  id    SERIAL PRIMARY KEY,
  name  TEXT NOT NULL UNIQUE,
  slug  TEXT NOT NULL UNIQUE   -- URL-friendly identifier
);

-- Step 2: Add category_id to products
-- The column is nullable (no NOT NULL) because products can be uncategorized
ALTER TABLE products
ADD COLUMN category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL;

-- Step 3: Add the index on the FK column
CREATE INDEX idx_products_category_id ON products(category_id);

-- Step 4: Migrate existing data (map text category to the new table)
INSERT INTO categories (name, slug) VALUES
  ('Electronics', 'electronics'),
  ('Accessories', 'accessories');

UPDATE products
SET category_id = (SELECT id FROM categories WHERE name = products.category)
WHERE category IN ('Electronics', 'Accessories');

-- The old text 'category' column can be dropped after migration
-- (in a real migration, this would be a separate step after verification)

Design decisions:

• category_id is nullable — NULL means uncategorized, which is valid
• ON DELETE SET NULL — deleting "Electronics" category makes those products uncategorized, not deleted
• Index on category_id — speeds up JOIN queries and category-change checks
• slug column — URL-friendly version of the name, also unique

---

Summary: Key Takeaways

• Referential integrity means every FK value must exist as a PK in the parent table — no dangling references
• REFERENCES parent_table(column) creates the FK constraint; add ON DELETE and ON UPDATE to specify behavior
• ON DELETE RESTRICT (default) — blocks deletion of parent rows with children; safe, explicit
• ON DELETE CASCADE — automatically deletes children; use when children are meaningless without parent
• ON DELETE SET NULL — clears the FK column; use when children remain valid without the parent; requires nullable column
• One-to-many: FK in the child table pointing to parent's PK
• Many-to-many: junction table with two FKs (one to each side) and a UNIQUE constraint on the combination
• One-to-one: FK column that is also PRIMARY KEY or UNIQUE
• Always add an index on FK columns — PostgreSQL doesn't do this automatically, and without it, parent-side deletes require full child table scans
• Prefer RESTRICT over CASCADE for business-critical parent records (users, orders) — explicit handling is safer than automatic deletion

---

What's Next?

You've built a fully constrained ShopFlow schema with proper types, constraints, and relationships. The next performance layer is understanding how to make queries against this schema fast.

The next step is Indexes: Making Queries Fast — where you'll learn what an index is, how the database decides when to use one, how to create indexes for the ShopFlow queries you've been writing, and how to read EXPLAIN ANALYZE output to confirm your indexes are working.