Loading published curriculum…

07 · HTTP and SQL foundations / LESSON 28 OF 102

SQL, keys, indexes, and transactions

Learn the database model before using an ORM.

Before this lesson: HTTP, REST, and JSON

Understand the topic

Primary keys identify rows; foreign keys protect references; unique and not-null constraints protect data. SELECT reads, INSERT creates, UPDATE changes, and DELETE removes rows. Review WHERE clauses before mutations.

Indexes help selected reads but cost storage and writes. Transactions commit or roll back related work; isolation controls concurrent visibility. Parameterized queries keep input separate from SQL syntax.

EXAMPLE · SQL

PostgreSQL · local disposable database

CREATE TABLE task (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  done BOOLEAN NOT NULL DEFAULT FALSE
);
INSERT INTO task(title) VALUES ('Learn SQL');
SELECT id, title FROM task WHERE done = FALSE ORDER BY id;
BEGIN;
UPDATE task SET done = TRUE WHERE id = 1;
ROLLBACK;

What to expect

The row title is Learn SQL. After rollback, done remains false.

YOUR TURN

Put it into practice

  1. 01Create an owner table and join through a foreign key.
  2. 02Try an invalid foreign key.
  3. 03Use EXPLAIN to inspect a query plan.

Pass a quiz or complete a lab to check today’s streak. Each lesson counts once per day, including when you review it later.

UNDER THE HOOD

Build the mental model

The database protects shared state even when many applications or requests write concurrently.

  1. 01A foreign key rejects references to absent rows. A unique constraint provides a concurrency-safe uniqueness rule that an application pre-check alone cannot guarantee.
  2. 02A join combines matching rows and can multiply them; count the cardinality before assuming one output row per task. LEFT JOIN preserves unmatched left rows.
  3. 03An index helps specific access patterns. A leading-column mismatch or low selectivity can make a scan cheaper; inspect EXPLAIN rather than assuming every index will be used.

A mistake worth avoiding

A transaction is not a substitute for choosing an isolation level and constraints.

DEBUGGING ROOM

Something broke. Where do you look?

Two concurrent signups pass an availability check and store the same username.

Before revealing the diagnosis, write down your first investigation step.

CHECK YOUR UNDERSTANDING

Which mechanism reliably prevents duplicate usernames during concurrent inserts?

Choose one answer

BUILD IT YOURSELF

Model owners and tasks

  1. 1.Create owner and task tables with a foreign key.
  2. 2.Write inner and left joins and explain their row counts.
  3. 3.Add a unique owner username and test duplicate inserts.

Acceptance checklist

Lab completion is your self-report. Knowledge checks do not certify project quality.

RETRIEVE IT FROM MEMORY

Why can a correct-looking pre-check still race?

Revisit this lesson in three days after a successful check or lab.

Ready for the next step?

Explain constraints, joins, indexes, and rollback.

Reading is separate from passing a knowledge check or completing a lab.

Go deeper · official references