Loading published curriculum…

08 · SQL and JDBC / LESSON 29 OF 102

Tables, keys, constraints, and normalization

A database schema protects invariants even when writes bypass your application.

Before this lesson: SQL, keys, indexes, and transactions

Understand the topic

A primary key identifies a row; a foreign key references another table. NOT NULL, UNIQUE, and CHECK constraints reject invalid states. Normalize repeated facts into separate tables so an update does not require synchronizing many copies.

Application validation gives helpful errors, while constraints defend concurrent writes. Choose ownership and deletion rules deliberately. A task title is usually not globally unique; its identifier should not depend on text that users can edit.

EXAMPLE · SQL

schema.sql

CREATE TABLE learners (id bigint PRIMARY KEY, email text NOT NULL UNIQUE);
CREATE TABLE work_items (
 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 owner_id bigint NOT NULL REFERENCES learners(id),
 title text NOT NULL CHECK (length(trim(title)) > 0),
 done boolean NOT NULL DEFAULT false
);

What to expect

Creating a task with an unknown owner or blank title is rejected by PostgreSQL. Use a disposable course database.

YOUR TURN

Put it into practice

  1. 01Create the two tables in a local PostgreSQL database.
  2. 02Attempt duplicate emails, missing owners, and blank titles.
  3. 03Verify: Each invalid insert fails at the database boundary and valid rows remain readable.

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

A database schema protects invariants even when writes bypass your application.

  1. 01A primary key identifies a row; a foreign key references another table. NOT NULL, UNIQUE, and CHECK constraints reject invalid states. Normalize repeated facts into separate tables so an update does not require synchronizing many copies.
  2. 02Application validation gives helpful errors, while constraints defend concurrent writes. Choose ownership and deletion rules deliberately. A task title is usually not globally unique; its identifier should not depend on text that users can edit.
  3. 03Trace the example: Creating a task with an unknown owner or blank title is rejected by PostgreSQL. Use a disposable course database.

A mistake worth avoiding

Two concurrent registrations create duplicate emails.

DEBUGGING ROOM

Something broke. Where do you look?

Two concurrent registrations create duplicate emails.

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

CHECK YOUR UNDERSTANDING

Can an application pre-check guarantee uniqueness during concurrent writes?

Choose one answer

BUILD IT YOURSELF

Create the two tables in a local PostgreSQL database.

  1. 1.Create the two tables in a local PostgreSQL database.
  2. 2.Attempt duplicate emails, missing owners, and blank titles.
  3. 3.Reproduce this failure, then repair it: Two concurrent registrations create duplicate emails.

Acceptance checklist

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

RETRIEVE IT FROM MEMORY

Can an application pre-check guarantee uniqueness during concurrent writes?

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

Ready for the next step?

Each invalid insert fails at the database boundary and valid rows remain readable.

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

Go deeper · official references