Foreign Keys - Referential Integrity

Introduction

Tables rarely exist in isolation. Customers place orders, orders contain products, users write comments. Foreign keys enforce these relationships, ensuring data integrity.

A foreign key is a column (or columns) that references the primary key of another table, creating a parent-child relationship.

Basic Foreign Key Syntax

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),  -- Foreign key
    total DECIMAL(10,2) NOT NULL
);

customer_id must exist in customers.id (or be NULL).

Foreign Key Relationships

One-to-Many

One customer can have many orders:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    total DECIMAL(10,2) NOT NULL
);

One-to-One

One user has one profile:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),  -- One-to-one
    bio TEXT,
    avatar_url VARCHAR(255)
);

user_id is both PRIMARY KEY and FOREIGN KEY!

Many-to-Many (Junction Table)

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

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

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    enrolled_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, course_id)
);

Referential Actions

Control what happens when referenced row is updated/deleted.

ON DELETE Actions

CASCADE

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE
);

Deleting customer → Deletes all their orders automatically

RESTRICT (Default)

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
);

Cannot delete customer if they have orders.

SET NULL

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL
);

Deleting customer → Sets customer_id to NULL in orders

SET DEFAULT

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET DEFAULT DEFAULT 0
);

Deleting customer → Sets customer_id to default value

NO ACTION

Similar to RESTRICT, but check can be deferred (advanced).

ON UPDATE Actions

Same options as ON DELETE:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id)
        ON UPDATE CASCADE  -- Update orders when customer ID changes
        ON DELETE CASCADE  -- Delete orders when customer is deleted
);

Choosing Referential Actions

Scenario 1: Orders and Customers

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
);

RESTRICT: Prevent accidental customer deletion

Scenario 2: Comments and Posts

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE
);

CASCADE: Deleting post should delete all comments

Scenario 3: Orders and Sales Reps

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    sales_rep_id INTEGER REFERENCES employees(id) ON DELETE SET NULL
);

SET NULL: Keep orders even if sales rep leaves

Foreign Key to Different Column

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE sessions (
    id SERIAL PRIMARY KEY,
    user_email VARCHAR(255) REFERENCES users(email)  -- Reference email, not id
);

Note: Referenced column must be UNIQUE or PRIMARY KEY!

Composite Foreign Keys

CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_shipments (
    id SERIAL PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    tracking_number VARCHAR(100),
    FOREIGN KEY (order_id, product_id) REFERENCES order_items(order_id, product_id)
);

Self-Referencing Foreign Keys

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INTEGER REFERENCES employees(id)  -- Self-reference
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Alice (CEO)', NULL),
    (2, 'Bob', 1),         -- Bob reports to Alice
    (3, 'Carol', 1);        -- Carol reports to Alice

Adding Foreign Keys to Existing Tables

ALTER TABLE orders
ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON DELETE CASCADE;

Dropping Foreign Keys

ALTER TABLE orders
DROP CONSTRAINT fk_customer;

Checking Foreign Key Constraints

Find All Foreign Keys

SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table,
    ccu.column_name AS foreign_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_name = 'orders';

Foreign Key Violations

-- ❌ Error: customer_id 999 doesn't exist
INSERT INTO orders (customer_id, total) VALUES (999, 100.00);

-- ✅ OK: customer_id 1 exists
INSERT INTO orders (customer_id, total) VALUES (1, 100.00);

-- ❌ Error: Cannot delete customer with orders (RESTRICT)
DELETE FROM customers WHERE id = 1;

Best Practices

1. Always Add Indexes on Foreign Keys

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Why: Speeds up JOINs and referential integrity checks

2. Name Foreign Key Constraints

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE CASCADE
);

3. Use NOT NULL When Appropriate

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id)  -- Every order must have a customer
);

4. Choose Appropriate ON DELETE Action

• CASCADE: Child data is meaningless without parent
• RESTRICT/NO ACTION: Protect against accidental deletion
• SET NULL: Child can exist independently

Practice Exercises

Exercise 1

Create authors and books tables with a one-to-many relationship.

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE RESTRICT
);

CREATE INDEX idx_books_author_id ON books(author_id);

Exercise 2

Create a many-to-many relationship between students and courses.

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

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

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id) ON DELETE CASCADE,
    course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    enrolled_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, course_id)
);

CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);

Key Takeaways

• ✅ Foreign keys enforce referential integrity
• ✅ ON DELETE CASCADE deletes child rows automatically
• ✅ ON DELETE RESTRICT prevents parent deletion
• ✅ ON DELETE SET NULL keeps child rows but removes reference
• ✅ Index foreign key columns for performance
• ✅ Name constraints for clarity
• ✅ One-to-many, one-to-one, many-to-many relationships

Next Steps

You've mastered foreign keys! In the next lesson, we'll explore Indexes—the secret to fast queries and high-performance databases.

Let's optimize!