Lesson 6 of 12 · 0 of 1 exercises solved

Primary Keys, Foreign Keys & Indexes

Keys keep your data consistent, and indexes keep queries fast as tables grow. A slow query is very often a missing index.

A 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 protecting your data from your own .

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.

-- 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

Practice

The orders table is queried by customer_id constantly. Create an index named idx_orders_customer on that column, then look at the query plan for a lookup by customer.

Exercise

Create an index

2 checks

-- create the index

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 2;

Output

Press Run to try your code, then Check answer to grade it.

Finished reading?1 exercise still open. You can come back to it later.

Tip: use the ← and → keys to move between lessons.