SQSQL · Lesson 6 of 8

Primary Keys, Foreign Keys & Indexes

Why was that query slow? Nine times out of ten: a missing index. Keys and indexes are how databases stay fast and consistent as tables grow to millions of rows.

A PRIMARY KEY uniquely identifies each row — usually an auto-incrementing integer. A FOREIGN KEY declares that a column's values must exist in another table, so you can't enroll a student id that doesn't exist. These constraints are the database protecting your data from your own bugs.

SQL

CREATE TABLE enrollments (
  id         INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id  INTEGER NOT NULL REFERENCES courses(id),
  UNIQUE (student_id, course_id)   -- can't enroll twice in one course
);

-- Foreign keys reject bad data:
INSERT INTO enrollments (student_id, course_id) VALUES (9999, 1);
-- ERROR: violates foreign key constraint

Without an index, WHERE email = '...' scans every row — fine at 100 rows, disastrous at 10 million. An index is a sorted lookup structure (usually a B-tree) the database maintains alongside the table, turning scans into instant lookups.

SQL

-- Create an index on a column you filter/join by often:
CREATE INDEX idx_students_email ON students(email);

-- See how a query executes (works in SQLite and Postgres):
EXPLAIN QUERY PLAN
SELECT * FROM students WHERE email = 'ada@example.com';
-- Before index: SCAN students
-- After index:  SEARCH students USING INDEX idx_students_email

⚠ Warning
Indexes aren't free — every INSERT/UPDATE must also update each index, and they consume disk. Index columns you actually query by; don't index everything 'just in case'.