KAHIBARO
Discord Login Register ☰
☰

9.5. Foreign Keys

Table of Contents

Understanding Foreign Keys

Foreign keys are one of the most important tools you have when you design relational databases. They are what connect tables together and allow you to model real world relationships in your data.

This chapter focuses on what foreign keys are, how they work, how to define them, and how to use them correctly in your backend applications.

What Is a Foreign Key?

A foreign key is a column, or a set of columns, in one table that refers to the primary key (or a unique key) of another table.

Think of it like this:

You usually use foreign keys to say something like:

Simple example

Two tables:

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL,
    total_cents INT NOT NULL
);

Right now, orders.user_id is just an integer. It could contain any number, even values that do not exist in users.id. If we add a foreign key constraint, we tell the database:

orders.user_id must always reference an existing users.id.
sql 
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users (id);

Now, the database enforces this relationship.

Key rule:
A foreign key ensures that the value in the referencing column must either be NULL or match an existing value in the referenced primary (or unique) key column.

Why Foreign Keys Matter

1. Data integrity

Foreign keys protect you from invalid references.

Without foreign keys, you might have:

With foreign keys, the database prevents such invalid data from being inserted or updated.

2. Self-documenting relationships

The existence of a foreign key tells you there is a relationship between tables. You do not need to guess.

This makes schemas easier to understand and maintain.

3. Helps ORMs and tools

Object Relational Mappers (ORMs) and database tools use foreign keys to:

If you skip foreign keys, many tools lose valuable information.

4. Prevents logical bugs

Imagine this bug:

Foreign keys can be configured to cascade or restrict such operations so you cannot accidentally break your data.

Basic Syntax of Foreign Keys

You can define foreign keys either when you create the table or later with ALTER TABLE.

Defining a foreign key when creating a table

sql 
CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    email   VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL,
    total_cents INT NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users (id)
);

Here:

You can also write it in a more compact way:

sql 
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES users(id),
    total_cents INT NOT NULL
);

The meaning is the same, but this syntax does not explicitly name the constraint.

Adding a foreign key to an existing table

If the table already exists, use ALTER TABLE:

sql 
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users (id);

If there is invalid data already (for example a user_id that does not exist in users), this statement will fail until you fix or delete that data.

Foreign Key Requirements

To create a foreign key, certain conditions must be true.

Matching data types

The referencing column and the referenced column must have compatible types.

Good:

sql 
users.id        INT PRIMARY KEY
orders.user_id  INT REFERENCES users(id)

Bad:

sql 
users.id        UUID PRIMARY KEY
orders.user_id  INT REFERENCES users(id)  -- type mismatch

The database will not allow such a definition.

Referencing unique or primary key

A foreign key usually references a primary key:

sql 
FOREIGN KEY (user_id) REFERENCES users(id);

It can also reference a column with a UNIQUE constraint:

sql 
CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY
);
CREATE TABLE profiles (
    id      SERIAL PRIMARY KEY,
    email   VARCHAR(255) NOT NULL,
    CONSTRAINT fk_profile_user_email
        FOREIGN KEY (email) REFERENCES users(email)
);

Here profiles.email references the unique users.email.

Important rule:
A foreign key must reference a column (or columns) that are either PRIMARY KEY or have a UNIQUE constraint. Otherwise the database cannot guarantee which row you are pointing to.

One-to-Many with Foreign Keys

The most common use of foreign keys is to model one-to-many relationships.

Examples:

Example: user and orders

sql 
CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES users(id),
    total_cents INT NOT NULL
);

How many relationships?

Inserting related data

sql 
INSERT INTO users (name)
VALUES ('Alice');   -- Suppose she gets id = 1
INSERT INTO orders (user_id, total_cents)
VALUES (1, 1000),   -- Alice's first order
       (1, 2500);   -- Alice's second order

Attempting to insert an order with a user that does not exist:

sql 
INSERT INTO orders (user_id, total_cents)
VALUES (999, 3000);

The database will respond with an error about a foreign key violation, since user 999 does not exist.

One-to-One with Foreign Keys

A one-to-one relationship can also be built with foreign keys, typically by enforcing uniqueness.

Example: a users table and a profiles table where each user has at most one profile.

sql 
CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    email   VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE profiles (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL UNIQUE,
    avatar_url  TEXT,
    bio         TEXT,
    CONSTRAINT fk_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
);

Now the relationship is one-to-one, because:

Many-to-Many with Foreign Keys (Join Tables)

Many-to-many relationships are usually implemented with a join table that has two foreign keys.

Example: students and courses.

Step 1: define the main tables

sql 
CREATE TABLE students (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
    id      SERIAL PRIMARY KEY,
    title   VARCHAR(255) NOT NULL
);

Step 2: define the join table with foreign keys

sql 
CREATE TABLE course_enrollments (
    student_id  INT NOT NULL REFERENCES students(id),
    course_id   INT NOT NULL REFERENCES courses(id),
    enrolled_at TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id)
);

Here:

Inserting enrollments:

sql 
INSERT INTO students (name) VALUES ('Alice');   -- id = 1
INSERT INTO students (name) VALUES ('Bob');     -- id = 2
INSERT INTO courses (title) VALUES ('Math');    -- id = 1
INSERT INTO courses (title) VALUES ('Physics'); -- id = 2
INSERT INTO course_enrollments (student_id, course_id)
VALUES
    (1, 1),  -- Alice in Math
    (1, 2),  -- Alice in Physics
    (2, 1);  -- Bob in Math

Any attempt to insert an enrollment with a student_id or course_id that does not exist will fail.

ON DELETE and ON UPDATE Actions

By default, if you try to delete or update a referenced row, the database will restrict the operation if it would break a foreign key.

You can customize this behavior with ON DELETE and ON UPDATE options.

Common actions

ActionDescription
RESTRICTPrevents the delete or update if there are referencing rows. Often the default behavior.
NO ACTIONSimilar to RESTRICT, check is done at the end of the statement or transaction.
CASCADEPropagates delete or update to referencing rows.
SET NULLSets the foreign key column to NULL in referencing rows when the referenced row is deleted/updated.
SET DEFAULTSets the foreign key column to its DEFAULT value.

Important caution:
ON DELETE CASCADE will automatically delete all child rows when a parent row is deleted. Use it only when you are absolutely sure this is the intended behavior.

Example: ON DELETE RESTRICT (default)

sql 
CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES users(id),
    total_cents INT NOT NULL
);

Behavior:

  1. Insert user and orders:
sql 
INSERT INTO users (name) VALUES ('Alice'); -- id = 1
INSERT INTO orders (user_id, total_cents)
VALUES (1, 1000), (1, 2000);
  1. Try to delete the user:
sql 
DELETE FROM users WHERE id = 1;

You get a foreign key violation. The orders still reference user 1, so the delete is not allowed.

Example: ON DELETE CASCADE

sql 
CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL,
    total_cents INT NOT NULL,
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

Now:

  1. Insert user and orders again.
  2. Delete the user:
sql 
DELETE FROM users WHERE id = 1;

The database will:

This can be useful for:

Example: ON DELETE SET NULL

Sometimes you want to keep the child rows but remove the reference.

Example: blog posts and authors. If an author is deleted, you might not want to delete their articles, but instead mark them as "orphaned".

sql 
CREATE TABLE authors (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100) NOT NULL
);
CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    author_id   INT REFERENCES authors(id) ON DELETE SET NULL,
    title       VARCHAR(255) NOT NULL,
    content     TEXT NOT NULL
);

NULL and Foreign Keys

A foreign key column can be NULL, and this does not violate the constraint.

Example:

sql 
CREATE TABLE tasks (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    assigned_to INT REFERENCES users(id)
);

Valid inserts:

sql 
INSERT INTO tasks (title, assigned_to) VALUES ('Unassigned task', NULL);
INSERT INTO tasks (title, assigned_to) VALUES ('Assigned to Alice', 1);

The only invalid case is when assigned_to is not NULL and does not match any existing user.

Composite Foreign Keys

Sometimes you need a foreign key that references multiple columns together. This is called a composite foreign key.

Example: suppose orders has a composite primary key (id, store_id).

sql 
CREATE TABLE stores (
    id      INT PRIMARY KEY,
    name    VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
    id          INT NOT NULL,
    store_id    INT NOT NULL,
    total_cents INT NOT NULL,
    PRIMARY KEY (id, store_id),
    CONSTRAINT fk_orders_store
        FOREIGN KEY (store_id) REFERENCES stores(id)
);

Now you create a table order_items that must reference the combination (id, store_id):

sql 
CREATE TABLE order_items (
    order_id    INT NOT NULL,
    store_id    INT NOT NULL,
    product     VARCHAR(100) NOT NULL,
    quantity    INT NOT NULL,
    PRIMARY KEY (order_id, store_id, product),
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id, store_id)
        REFERENCES orders(id, store_id)
);

Rules:

Key rule for composite foreign keys:
The number of columns and their order must exactly match the referenced primary or unique key definition.

Practical Examples for Backend Developers

Here are some common patterns you will see in real backend projects.

Example 1: Users and posts

sql 
CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    email   VARCHAR(255) NOT NULL UNIQUE,
    name    VARCHAR(100) NOT NULL
);
CREATE TABLE posts (
    id        SERIAL PRIMARY KEY,
    user_id   INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title     VARCHAR(255) NOT NULL,
    content   TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Behavior:

Use this pattern if posts are meaningless without the user.

Example 2: Orders and order items

sql 
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    price_cents INT NOT NULL
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES users(id),
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
    id          SERIAL PRIMARY KEY,
    order_id    INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  INT NOT NULL REFERENCES products(id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price_cents INT NOT NULL
);

Example 3: Optional profile picture

sql 
CREATE TABLE images (
    id      SERIAL PRIMARY KEY,
    url     TEXT NOT NULL
);
CREATE TABLE users (
    id              SERIAL PRIMARY KEY,
    email           VARCHAR(255) NOT NULL UNIQUE,
    profile_image_id INT REFERENCES images(id) ON DELETE SET NULL
);

Common Mistakes and How to Avoid Them

Mistake 1: No foreign keys at all

Sometimes developers skip foreign keys because they think:

Result:

Best practice:

Mistake 2: Wrong ON DELETE behavior

You might accidentally:

Best practice:

Mistake 3: Mismatched types

If you change a primary key type but forget to change all related foreign keys, you will run into errors.

Example:

The fix is to keep types consistent.

Mistake 4: Missing indexes on foreign keys

Foreign keys enforce integrity, but for good performance you often want an index on the foreign key column, especially if:

Some databases automatically create indexes for foreign keys, some do not. For safety, many teams create indexes manually.

Example:

sql 
CREATE INDEX idx_orders_user_id ON orders(user_id);

How Foreign Keys Affect Queries

Foreign keys themselves do not change how you write JOINs, but they often reflect how you will join tables.

Example:

sql 
SELECT
    users.id,
    users.email,
    orders.id AS order_id,
    orders.total_cents
FROM users
JOIN orders ON orders.user_id = users.id
WHERE users.id = 1;

This join uses the foreign key relationship. The foreign key ensures there are no orders.user_id that point to non-existent users.

When you design your schema with correct foreign keys, you get:

Summary

With a solid understanding of foreign keys, you are ready to explore more specific relationship patterns, such as one-to-one, one-to-many, and many-to-many relationships, and how they shape your database schema.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3039 / 24h
Impressum / Imprint
Datenschutz / Data Policy