Understanding Relationships and Keys

Introduction

Real-world data is interconnected. Customers place orders, orders contain products, products have categories. In relational databases, we model these connections using relationships between tables, enforced through keys.

Understanding relationships and keys is foundational to database design and writing effective queries with JOINs.

Why Multiple Tables?

The Problem with One Giant Table

Imagine storing all e-commerce data in one table:

orders_everything table:
┌────┬───────────┬────────────────┬────────┬──────────────┬────────┬──────────┐
│ id │cust_name  │ cust_email     │prod_id │ product_name │ price  │ quantity │
├────┼───────────┼────────────────┼────────┼──────────────┼────────┼──────────┤
│ 1  │Alice Smith│alice@email.com │ 101    │ Laptop       │ 999.99 │ 1        │
│ 2  │Alice Smith│alice@email.com │ 201    │ Mouse        │ 25.99  │ 2        │
│ 3  │Bob Jones  │bob@email.com   │ 101    │ Laptop       │ 999.99 │ 1        │
└────┴───────────┴────────────────┴────────┴──────────────┴────────┴──────────┘

Problems:

• Data redundancy: Alice's info repeated
• Update anomalies: Change Alice's email → must update multiple rows
• Insertion anomalies: Can't add a customer without an order
• Deletion anomalies: Delete all orders → lose customer info
• Wasted space: Product details repeated for each order

The Solution: Separate Tables

-- Customers table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

-- Products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

-- Orders table (references customers and products)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER
);

Benefits:

• Each fact stored once
• Easy updates (change customer email in one place)
• No redundancy
• Enforced data integrity

Primary Keys

What is a Primary Key?

A Primary Key uniquely identifies each row in a table.

Requirements:

• Must be UNIQUE
• Cannot be NULL
• Only one primary key per table (can be multiple columns though)

Examples

Single Column Primary Key:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- Auto-incrementing integer
    email VARCHAR(255)
);

Natural Primary Key:

CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY,  -- 'US', 'UK', 'FR'
    name VARCHAR(100)
);

Composite Primary Key (Multiple Columns):

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrolled_at TIMESTAMPTZ,
    PRIMARY KEY (student_id, course_id)  -- Combination must be unique
);

SERIAL vs Manual IDs

SERIAL (Recommended for most cases):

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,  -- Auto-generates 1, 2, 3, ...
    title VARCHAR(200)
);

INSERT INTO posts (title) VALUES ('First Post');
-- id automatically becomes 1

INSERT INTO posts (title) VALUES ('Second Post');
-- id automatically becomes 2

Manual IDs:

CREATE TABLE products (
    sku VARCHAR(20) PRIMARY KEY,  -- 'PROD-001', 'PROD-002'
    name VARCHAR(100)
);

INSERT INTO products (sku, name) VALUES ('PROD-001', 'Widget');

Foreign Keys

What is a Foreign Key?

A Foreign Key is a column that references the primary key of another table, creating a relationship.

Basic Foreign Key

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

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),  -- Foreign key
    order_date DATE
);

customer_id in orders must match an id in customers.

Enforcing Referential Integrity

-- Insert customers
INSERT INTO customers (id, name) VALUES (1, 'Alice'), (2, 'Bob');

-- ✅ Valid: customer 1 exists
INSERT INTO orders (customer_id, order_date)
VALUES (1, '2024-01-15');

-- ❌ Error: customer 999 doesn't exist
INSERT INTO orders (customer_id, order_date)
VALUES (999, '2024-01-15');

Error:

ERROR: insert or update on table "orders" violates foreign key constraint
DETAIL: Key (customer_id)=(999) is not present in table "customers".

Types of Relationships

One-to-Many (1:N)

Most common relationship type

One customer can have many orders, but each order belongs to one customer.

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

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),  -- Many orders → One customer
    total DECIMAL(10,2)
);

Diagram:

Customers          Orders
┌────────┐        ┌─────────────┐
│ id (PK)│◄──────┤customer_id  │
│ name   │        │ id (PK)     │
└────────┘        │ total       │
    1             └─────────────┘
    ↓                    N
  One                  Many

Example Data:

-- One customer
INSERT INTO customers (id, name) VALUES (1, 'Alice');

-- Many orders for that customer
INSERT INTO orders (customer_id, total) VALUES
    (1, 100.00),
    (1, 75.50),
    (1, 200.00);

One-to-One (1:1)

One row in table A relates to exactly one row in table B.

Example: User and Profile

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

CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),  -- Also the PK
    bio TEXT,
    avatar_url VARCHAR(500)
);

Each user has exactly one profile, each profile belongs to one user.

When to use:

• Split large tables (some columns rarely accessed)
• Security (sensitive data in separate table)
• Optional extensions (not all users have profiles)

Many-to-Many (N:M)

Multiple rows in table A relate to multiple rows in table B.

Example: Students and Courses

• One student can enroll in many courses
• One course can have many students

Solution: Junction Table (Bridge Table)

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

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200)
);

-- Junction table
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)  -- Composite key
);

Diagram:

Students      Enrollments      Courses
┌────┐        ┌──────────┐     ┌────┐
│ id │◄──────┤student_id│     │ id │
│name│        │course_id ├────►│title│
└────┘        └──────────┘     └────┘
  N                              M

Example Data:

-- Students
INSERT INTO students (id, name) VALUES (1, 'Alice'), (2, 'Bob');

-- Courses
INSERT INTO courses (id, title) VALUES (101, 'SQL Basics'), (102, 'Python');

-- Enrollments (many-to-many relationships)
INSERT INTO enrollments (student_id, course_id) VALUES
    (1, 101),  -- Alice in SQL
    (1, 102),  -- Alice in Python
    (2, 101);  -- Bob in SQL

Foreign Key Constraints: ON DELETE and ON UPDATE

ON DELETE CASCADE

When parent row is deleted, child rows are automatically deleted.

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

-- Delete customer
DELETE FROM customers WHERE id = 1;

-- All orders for customer 1 are automatically deleted!

ON DELETE RESTRICT (Default)

Prevents deletion of parent if children exist.

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

-- ❌ Error if customer has orders
DELETE FROM customers WHERE id = 1;

ON DELETE SET NULL

Sets foreign key to NULL when parent is deleted.

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

-- Delete customer
DELETE FROM customers WHERE id = 1;

-- Orders remain, but customer_id becomes NULL

ON DELETE SET DEFAULT

Sets foreign key to default value when parent is deleted.

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

ON UPDATE CASCADE

When parent key is updated, child keys update automatically.

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

-- Update customer ID
UPDATE customers SET id = 100 WHERE id = 1;

-- All orders' customer_id automatically updated to 100

Best Practices for Keys and Relationships

1. Always Use Primary Keys

-- ❌ No primary key
CREATE TABLE logs (
    message TEXT,
    created_at TIMESTAMPTZ
);

-- ✅ Has primary key
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMPTZ
);

2. Name Foreign Keys Clearly

-- ❌ Unclear
CREATE TABLE orders (
    cid INTEGER REFERENCES customers(id)
);

-- ✅ Clear
CREATE TABLE orders (
    customer_id INTEGER REFERENCES customers(id)
);

3. Index Foreign Keys

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Makes JOINs faster!

4. Use SERIAL for Most Primary Keys

-- ✅ Simple, automatic, guaranteed unique
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255)
);

5. Consider UUID for Distributed Systems

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255)
);
-- No ID conflicts across multiple databases

6. Use Appropriate ON DELETE Actions

• CASCADE: Delete children when parent is deleted (orders when customer is deleted)
• RESTRICT: Prevent deletion if children exist (can't delete product if in orders)
• SET NULL: Keep children but remove relationship (optional parent)

Naming Conventions

Tables

• Plural nouns: customers, orders, products
• Or singular: customer, order, product
• Be consistent!

Primary Keys

• Usually id
• Or table_name_id: customer_id, order_id

Foreign Keys

• parent_table_singular_id: customer_id, product_id
• Or parent_id if context is clear

Practice: Designing Relationships

Exercise 1: Blog System

Design tables for a blog with:

• Authors (have multiple posts)
• Posts (belong to one author, have multiple comments)
• Comments (belong to one post and one user)

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE,
    title VARCHAR(200),
    content TEXT,
    published_at TIMESTAMPTZ
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    author_id INTEGER REFERENCES authors(id) ON DELETE SET NULL,
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Exercise 2: School Database

Design tables for:

• Students (enroll in multiple courses)
• Courses (have multiple students)
• Teachers (teach multiple courses)

CREATE TABLE teachers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    teacher_id INTEGER REFERENCES teachers(id)
);

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

-- Many-to-many: students and courses
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    grade DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id)
);

Key Takeaways

• ✅ Primary Key: Uniquely identifies each row (UNIQUE, NOT NULL)
• ✅ Foreign Key: References primary key of another table
• ✅ One-to-Many: Most common (customer → orders)
• ✅ Many-to-Many: Requires junction table (students ↔ courses)
• ✅ One-to-One: Rare, used for table splitting
• ✅ Referential Integrity: Foreign keys enforce valid relationships
• ✅ ON DELETE/UPDATE: Control what happens when parent changes

Next Steps

You now understand how tables relate to each other! In the next lesson, we'll learn INNER JOIN—how to query data from multiple related tables and combine them into a single result set.

Get ready for one of SQL's most powerful features!