Home/Learn/SQL/Primary Key & Foreign Key Constraints

Primary Key & Foreign Key Constraints

Beginner

Schema & DDL

A primary key uniquely identifies each row; a foreign key enforces referential integrity by linking a column to the primary key of another table.

Overview

The PRIMARY KEY constraint combines NOT NULL and UNIQUE, and the database automatically creates a B-Tree index on it. A FOREIGN KEY constraint requires that every non-null value in the referencing column exists in the referenced table's primary (or unique) key column, preventing orphaned rows. ON DELETE / ON UPDATE actions (CASCADE, SET NULL, RESTRICT, NO ACTION) define what happens to child rows when the parent is modified. Poor FK design — or skipping FKs entirely for "performance" — is a common source of data corruption in microservice databases.

Defining PKs and FKs

Composite primary keys are allowed but should be avoided for surrogate-key tables — they make FK references verbose. Use surrogate BIGINT keys and add a UNIQUE constraint on the natural key instead.

SQL — FK with ON DELETE/ON UPDATE actions

-- PostgreSQL / MySQL
CREATE TABLE departments (
    id   BIGSERIAL    PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id            BIGSERIAL    PRIMARY KEY,
    full_name     VARCHAR(200) NOT NULL,
    department_id BIGINT       NOT NULL,
    manager_id    BIGINT,                          -- nullable self-reference
    hired_at      DATE         NOT NULL,

    CONSTRAINT fk_emp_dept
        FOREIGN KEY (department_id) REFERENCES departments(id)
        ON DELETE RESTRICT    -- block dept deletion if employees exist
        ON UPDATE CASCADE,    -- propagate dept PK change (rare but safe)

    CONSTRAINT fk_emp_manager
        FOREIGN KEY (manager_id) REFERENCES employees(id)
        ON DELETE SET NULL    -- manager leaves → employees become unmanaged
);

Referential Actions Compared

Choose the referential action based on business semantics. RESTRICT and NO ACTION both block the parent delete but differ in when the check fires (immediate vs end of transaction). CASCADE is convenient but dangerous on deeply nested hierarchies.

SQL — referential action comparison

-- CASCADE: deleting a user removes all their orders (useful for GDPR purge)
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE;

-- SET NULL: deleting a category un-categorises products (keeps products alive)
ALTER TABLE products
    ADD CONSTRAINT fk_products_category
    FOREIGN KEY (category_id) REFERENCES categories(id)
    ON DELETE SET NULL;

-- RESTRICT (default in most DBs): prevents accidental parent deletion
-- DELETE FROM departments WHERE id = 5;
-- ERROR: update or delete on table "departments" violates foreign key constraint

-- Anti-pattern: dropping FK for "performance" then querying orphaned data
-- SELECT * FROM orders o JOIN users u ON o.user_id = u.id
-- → may silently miss orders whose user was deleted

JPA Relationship Mapping

JPA @ManyToOne maps directly to a foreign key column. The cascade attribute on the Java side is separate from the database ON DELETE action — both can exist simultaneously.

Java — JPA FK mapping with FetchType.LAZY

@Entity
public class Employee {

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "department_id", nullable = false,
                foreignKey = @ForeignKey(name = "fk_emp_dept"))
    private Department department;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "manager_id",
                foreignKey = @ForeignKey(name = "fk_emp_manager"))
    private Employee manager;  // self-referencing FK
}

Key Points to Remember

1PRIMARY KEY = NOT NULL + UNIQUE; the DB automatically indexes it.
2FOREIGN KEY prevents orphaned rows and is enforced at the storage engine level.
3ON DELETE CASCADE removes children automatically — use carefully in complex hierarchies.
4ON DELETE SET NULL is useful when child records should survive parent deletion.
5Always name FK constraints explicitly (fk_table_column) for readable error messages.
6In JPA, FetchType.LAZY on @ManyToOne prevents N+1 query explosions.

Interview Questions

What is the difference between ON DELETE CASCADE and ON DELETE SET NULL?

EasyAmazon

Can a table have multiple foreign keys? Can a foreign key reference a non-primary key column?

MediumMicrosoft

Why is it sometimes recommended to disable FK constraints in bulk-load scenarios, and what risks does that carry?

MediumUber

How does JPA cascade differ from database ON DELETE CASCADE?

HardNetflix

Explain the difference between RESTRICT and NO ACTION in PostgreSQL.

HardGoogle

Ask Aria about Primary Key & Foreign Key Constraints

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

CREATE TABLE, Data Types & Constraints

ALTER TABLE