Relationships & Keys

Keys are the backbone of relational databases. They uniquely identify rows, link tables together, and enforce data integrity. Without keys, a relational database is just a bunch of spreadsheets with no connections.

Primary Keys

A primary key uniquely identifies each row in a table. No two rows can have the same primary key, and it can never be NULL.

Natural Keys vs Surrogate Keys

A natural key uses a real-world value that’s already unique — like an email address or a Social Security Number.

A surrogate key is an artificial value we generate — like an auto-incrementing integer or a UUID.

-- Natural key: using email (it's already unique)
CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY,
    name VARCHAR(100)
);

-- Surrogate key: auto-incrementing integer
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- 1, 2, 3, 4...
    email VARCHAR(255) UNIQUE,
    name VARCHAR(100)
);

-- Surrogate key: UUID
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE,
    name VARCHAR(100)
);

Auto-increment vs UUID — When to Use Which?

Rule of thumb: Use auto-increment for internal IDs. Use UUIDs when IDs are exposed in URLs or when working with distributed databases.

Foreign Keys

A foreign key is a column that references the primary key of another table. It’s how we create relationships between tables and enforce referential integrity.

Referential integrity means: if a row in the orders table says user_id = 5, there MUST be a user with id = 5 in the users table. The database enforces this automatically.

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

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10,2),
    -- Foreign key constraint
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);

-- This WORKS (user 1 exists)
INSERT INTO users (id, name) VALUES (1, 'Manish');
INSERT INTO orders (user_id, total) VALUES (1, 99.99);

-- This FAILS (user 999 doesn't exist)
INSERT INTO orders (user_id, total) VALUES (999, 49.99);
-- ERROR: insert or update violates foreign key constraint "fk_user"

ON DELETE Behavior

What happens when we delete a parent row that has child rows referencing it? We have several options:

-- RESTRICT (default): prevent deletion if references exist
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
-- "Can't delete this user — they have orders!"

-- CASCADE: delete child rows too
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
-- Delete the user → all their orders are automatically deleted too

-- SET NULL: set the FK to NULL
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
-- Delete the user → orders remain but user_id becomes NULL

-- SET DEFAULT: set the FK to its default value
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET DEFAULT
-- Delete the user → user_id is set to whatever DEFAULT is defined

Similarly, ON UPDATE CASCADE is useful when the referenced key might change:

-- If user's id changes, update all orders that reference it
FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

Which ON DELETE to Use?

• CASCADE — when child data is meaningless without the parent (order items when an order is deleted)
• RESTRICT — when deletion should be blocked (can’t delete a department if employees are assigned)
• SET NULL — when the relationship is optional (the comment still exists, but the author is gone)

Composite Keys

A composite key is a primary key made of two or more columns together. Neither column is unique on its own, but the combination is.

-- A student can enroll in many courses
-- A course can have many students
-- The combination (student_id, course_id) is unique

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    enrolled_at TIMESTAMP DEFAULT NOW(),
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id)  -- composite key
);

-- Student 1 can enroll in Course 101 and Course 102
-- But Student 1 can't enroll in Course 101 twice

Junction Tables for Many-to-Many

We can’t directly represent a many-to-many relationship with a foreign key. We need a junction table (also called a bridge table, join table, or linking table).

-- Students ←→ Courses (many-to-many)
-- A student takes many courses. A course has many students.

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

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

-- Junction table: the "glue" between them
CREATE TABLE student_courses (
    student_id INT REFERENCES students(id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(id) ON DELETE CASCADE,
    enrolled_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id)
);

-- Enroll student 1 in courses 1 and 2
INSERT INTO student_courses (student_id, course_id) VALUES (1, 1);
INSERT INTO student_courses (student_id, course_id) VALUES (1, 2);

-- Find all courses for a student
SELECT c.title
FROM courses c
JOIN student_courses sc ON c.id = sc.course_id
WHERE sc.student_id = 1;

Junction tables can also carry extra data about the relationship itself — like when the enrollment happened, what grade was received, etc.

Unique Constraints

Besides the primary key, we often need other columns to be unique:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,              -- single column unique
    username VARCHAR(50) UNIQUE,
    UNIQUE (first_name, last_name)          -- composite unique (rare)
);

The difference between a UNIQUE constraint and a PRIMARY KEY:

• A table can have many UNIQUE constraints but only one PRIMARY KEY
• UNIQUE columns can be NULL (and multiple NULLs are allowed in most databases)
• PRIMARY KEY = UNIQUE + NOT NULL

Check Constraints

While we’re talking about constraints, CHECK constraints are worth mentioning:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),           -- must be positive
    discount DECIMAL(3,2) CHECK (discount BETWEEN 0 AND 1),  -- 0-100%
    status VARCHAR(20) CHECK (status IN ('active', 'archived', 'draft'))
);

Interview Tip

When designing a schema in an interview, always start by identifying the relationships and their cardinalities. For 1:N, put the FK on the “many” side. For M:N, create a junction table. Know the ON DELETE options (CASCADE, RESTRICT, SET NULL) and when to use each. And be ready to explain natural vs surrogate keys — most interviewers have a preference and like to debate it.