Key idea
A schema is the database's description of your data: which tables exist, which columns each one has, what type each column holds, and how tables point at each other. Find the keys and you can read the relationships.
Lesson 1.9.2 said a relational database keeps tables linked by keys. Here is what that looks like for a small to-do app, where each user has many tasks:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE
);
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
title TEXT NOT NULL,
done BOOLEAN NOT NULL DEFAULT false,
due_date DATE
);
Tables, rows and columns
A table holds one kind of thing: users holds people and tasks holds to-dos. Each row is one of them, and each column is one fact about it.
Types and rules
Every column has a type: INTEGER for whole numbers, TEXT for strings, BOOLEAN for true or false, DATE for a day. The database refuses a value of the wrong type, so bad data is stopped on the way in rather than found months later.
Rules sit next to the type:
NOT NULL: the column must have a value, so a task without a title is refused.UNIQUE: no two rows share the value, so two accounts can't use one email.DEFAULT: the value used when you don't give one, so new tasks start not done.
due_date has no NOT NULL, so it can be empty. Empty is NULL, which means "no value": not zero, and not an empty string.
Primary keys
Each table's id is its primary key: a value that picks out exactly one row, never repeats and shouldn't change. The database usually fills it in for you. Other tables use it to point at that row.
Foreign keys and one-to-many
tasks.user_id is a foreign key: it holds the id of a row in users. REFERENCES users(id) tells the database to check it, so a task can't belong to a user who doesn't exist, and a user who still has tasks can't be deleted by accident.
This is a one-to-many relationship: one user, many tasks. The foreign key always sits on the "many" side, because each task has exactly one owner while a user has any number of tasks.
To list one user's tasks, the database joins the two tables on that key:
SELECT tasks.title, tasks.done
FROM tasks
JOIN users ON users.id = tasks.user_id
WHERE users.email = '[email protected]';
Other relationships, and indexes
One-to-one: each user has one profile. Put user_id in profiles and make it UNIQUE, so no user gets two.
Many-to-many: a task can have many tags, and a tag belongs to many tasks. Neither table can hold the link, so a third table, task_tags, holds pairs of foreign keys: task_id and tag_id.
Indexes: a foreign key you search by, such as tasks.user_id, usually gets an index, so finding one user's tasks doesn't mean reading every task. PostgreSQL indexes primary keys and UNIQUE columns for you, but not foreign keys.
SQLite has no real BOOLEAN or DATE types: it stores true and false as the INTEGERs 1 and 0, and dates as TEXT such as 2026-09-28. It also checks foreign keys only after PRAGMA foreign_keys = ON; PostgreSQL and MySQL check them by default.
Check yourself