One-to-many

One author has many books. One user has many orders. One board has many lists. One-to-many is the most common relationship in any database, and if you get this pattern right, you can model the majority of real-world data.

So far, our database has a users table, created in migration 001_create_users.sql, and a bio column we added in 002_add_bio_to_users.sql. We are building a book review platform: people sign up, read books, and later we will let them rate and review what they read. The next thing the schema needs is books, and books do not sit on their own. Every book has an author, and most authors have written more than one book. That is a one-to-many relationship, and shipping it through a proper migration is exactly the next step.

In this lesson, we will set up a one-to-many relationship between authors and books using foreign keys, insert related data across the two tables, and start querying across them.

Foreign keys

A foreign key is a column that references the primary key of another table. It creates a link between two rows.

Think about our book platform. You have authors, and each author can write many books. The authors table stores author information. The books table stores book information. But each book needs to know who wrote it. That is where the foreign key comes in: a column in the books table that points back to a row in the authors table.

Before we turn this into a migration file, let’s look at the shape we want:

CREATE TABLE authors (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  bio TEXT
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  genre TEXT NOT NULL,
  description TEXT,
  published_at TEXT,
  author_id INTEGER NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

The key line is the one with the foreign key. author_id in the books table points to id in the authors table. Every book is linked to exactly one author. An author can be linked to many books. That is the one-to-many relationship in a nutshell. The other columns (genre, description, published_at) are there because the book platform will eventually want to filter by genre, show a book page with a description, and sort by publication date. We add them now so later lessons (filtering, indexes, full-text search) have something to work with.

Why the foreign key goes on the “many” side

This is a detail worth pausing on, because it trips up beginners all the time.

Each book has one author, so author_id is a single value. That fits perfectly in a column. But what if you tried to put the relationship on the author side instead? You would need a column that stores a list of book IDs. SQL columns hold single values, not arrays.

-- WRONG: trying to put the relationship on the "one" side
CREATE TABLE authors (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  book_ids TEXT  -- "1,2,3", comma-separated strings again
);

-- RIGHT: foreign key on the "many" side
CREATE TABLE books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL,  -- single value, points to one author
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

The wrong approach is the same comma-separated values problem from the last section. You cannot query it, you cannot index it, and it falls apart as soon as you have more than a handful of related rows. The right approach uses a single column that holds one reference.

The rule: the foreign key always goes on the “many” side of the relationship.

Enabling foreign key enforcement

Here is something that catches people off guard. SQLite does not enforce foreign keys by default. Even with the FOREIGN KEY clause in your table definition, SQLite will happily let you insert a book with author_id = 999 even if no author with that ID exists.

To turn on enforcement, you have to run this pragma:

PRAGMA foreign_keys = ON;

With this pragma set, SQLite will reject any insert or update that references a row that does not exist in the parent table.

[!WARNING] PRAGMA foreign_keys = ON must be set on every database connection, not just once. It does not persist. If you close and reopen the database, foreign keys are off again until you set the pragma. In a later lesson we will pull this pragma into the connection setup so it runs automatically every time your app opens a database connection. For now, make sure to run it at the top of your SQL sessions.

Writing migration 003

Now let’s turn this into a real migration. Both tables belong together: books cannot exist in a useful form until authors is there for the foreign key to point at. So we put them in the same file and let the transaction tie them together.

-- migrations/003_create_authors_and_books.sql
BEGIN;

CREATE TABLE authors (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  bio TEXT
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  genre TEXT NOT NULL,
  description TEXT,
  published_at TEXT,
  author_id INTEGER NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

INSERT INTO schema_migrations (version, name)
VALUES ('003', 'create_authors_and_books');

COMMIT;

A few things to notice. The order inside the file matters: authors is created first because books.author_id references it, and the referenced table has to exist before the reference can be declared. The whole thing is wrapped in BEGIN and COMMIT, so if either CREATE TABLE fails, the transaction rolls back and the database is left in exactly the state it was in before you started. And the migration does not touch users, because users already exists from migration 001.

Run the migration with your runner and you now have both an authors table and a books table, ready for data.

Inserting related data

With the tables in place, let’s add some data. Order matters here. You must insert the parent row first (the author), then the child rows (the books). The child rows reference the parent, so the parent has to exist before you can point to it.

PRAGMA foreign_keys = ON;

INSERT INTO authors (name, bio) VALUES
  ('Ernest Hemingway', 'American novelist and short story writer.'),
  ('Ursula K. Le Guin', 'American novelist best known for science fiction and fantasy.');

Now let’s add some books. Hemingway (id 1) gets two books, and Le Guin (id 2) gets one:

INSERT INTO books (title, genre, published_at, author_id) VALUES
  ('The Old Man and the Sea', 'fiction', '1952-09-01', 1),
  ('A Farewell to Arms',      'fiction', '1929-09-27', 1),
  ('A Wizard of Earthsea',    'fantasy', '1968-11-01', 2);

Each book has an author_id that matches an existing author. Hemingway has two books, Le Guin has one. The relationship is stored cleanly, no string matching, no duplication.

Inserting an author with their first book

A real app often wants to add an author and their first book in a single operation. “Add this new author along with the one book we already know about” only makes sense as a pair: if the book insert fails (bad genre, missing field), we do not want the author row to stick around alone. Wrap the pair in a transaction:

BEGIN;
  INSERT INTO authors (name, bio) VALUES ('Octavia E. Butler', 'American science fiction writer.');
  INSERT INTO books (title, genre, published_at, author_id)
    VALUES ('Kindred', 'science-fiction', '1979-06-01', last_insert_rowid());
COMMIT;

last_insert_rowid() is a built-in SQLite function that returns the id of the most recently inserted row on the current connection. In this case it returns the new author’s id, which we feed straight into the book insert so the foreign key points at the author we just created. Because both statements sit inside a single transaction, they either both land or neither does.

Rejecting bad foreign keys

Now let’s see what happens when you try to insert a book for an author that does not exist:

INSERT INTO books (title, genre, author_id) VALUES ('Ghost Novel', 'fiction', 999);

SQLite rejects it: FOREIGN KEY constraint failed. The author with id 999 does not exist, and the foreign key constraint prevents the insert. This is exactly why we enabled PRAGMA foreign_keys = ON.

Querying across tables with JOIN

Right now you can query each table individually. But the real power of a relational database is querying across tables at the same time. You want to see a book and the name of the author who wrote it, all in one result.

This is what JOIN does. It combines rows from two tables based on a related column.

SELECT books.title, books.published_at, authors.name
FROM books
JOIN authors ON books.author_id = authors.id;

Let’s read this slowly.

FROM books says we are starting with the books table. JOIN authors says we want to bring in data from the authors table too. ON books.author_id = authors.id is the condition that connects the two tables: for each book, find the author whose id matches the book’s author_id.

The result looks like this:

title                    published_at  name
-----------------------  ------------  ----------------
The Old Man and the Sea  1952-09-01    Ernest Hemingway
A Farewell to Arms       1929-09-27    Ernest Hemingway
A Wizard of Earthsea     1968-11-01    Ursula K. Le Guin

Each book is paired with the name of the author who wrote it. Hemingway’s name appears twice because he has two books.

You can also filter the results. To get only Hemingway’s books:

SELECT books.title, authors.name
FROM books
JOIN authors ON books.author_id = authors.id
WHERE authors.name = 'Ernest Hemingway';

Including authors with no books using LEFT JOIN

A regular JOIN only returns rows where there is a match in both tables. If an author has no books yet, they do not appear in the results at all.

Sometimes you want to see all authors, even those with zero books. That is what LEFT JOIN does. It returns every row from the left table (the one after FROM), and fills in NULL for columns from the right table when there is no match.

Let’s add an author with no books to see this in action:

INSERT INTO authors (name) VALUES ('New Author');

Now compare JOIN and LEFT JOIN:

-- Regular JOIN: New Author does not appear
SELECT authors.name, books.title
FROM authors
JOIN books ON books.author_id = authors.id;

-- LEFT JOIN: New Author appears with NULL for title
SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON books.author_id = authors.id;

The LEFT JOIN result:

name               title
-----------------  -----------------------
Ernest Hemingway   The Old Man and the Sea
Ernest Hemingway   A Farewell to Arms
Ursula K. Le Guin  A Wizard of Earthsea
New Author         NULL

New Author shows up with NULL for the title, because they have no books. This is useful when you want to count books per author, including authors with zero:

SELECT authors.name, COUNT(books.id) AS book_count
FROM authors
LEFT JOIN books ON books.author_id = authors.id
GROUP BY authors.id;

name               book_count
-----------------  ----------
Ernest Hemingway   2
Ursula K. Le Guin  1
New Author         0

We are using GROUP BY authors.id to group the results by author, and COUNT(books.id) to count how many books each author has. New Author gets a count of 0 because COUNT does not count NULLs.

We will cover JOINs in much more detail in a later section of this course. For now, JOIN and LEFT JOIN are enough to work with one-to-many relationships.

ON DELETE behavior

Here is a scenario to think about. You delete an author. What should happen to their books?

This is not an edge case. It comes up in every application. The ON DELETE clause on the foreign key controls what happens:

-- Option 1: Delete the author's books too
FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE

-- Option 2: Set author_id to NULL (books become orphaned)
FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE SET NULL

-- Option 3: Prevent deleting an author who has books (default)
FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE RESTRICT

CASCADE automatically deletes related rows. Delete an author and all their books disappear too. This is convenient but dangerous. One delete can cascade through your entire database if you are not careful.

SET NULL keeps the books but removes the link. The books still exist, but they no longer belong to anyone. This requires author_id to allow NULL, so you would need to remove the NOT NULL constraint on it.

RESTRICT is the default behavior. It prevents the delete entirely. If an author has books, you cannot delete that author until you delete the books first. This is the safest option because it forces you to be explicit about what happens to related data.

Which one should you use? It depends on the relationship. For authors and books, RESTRICT is usually the right call because deleting an author by accident should not erase their entire catalog. For something like an order and its line items, CASCADE is common because a line item has no meaning without its order. Think about what makes sense for your data, and if you change your mind later, ship the change as a new migration.

Exercises

Exercise 1: Write the 003_create_authors_and_books.sql migration shown above and run it through your migration runner. Insert two authors and three books (at least one author should have multiple books). Use a JOIN to display each book with its author’s name.

Exercise 2: Enable PRAGMA foreign_keys = ON and try inserting a book with an author_id that does not exist. Verify that SQLite rejects it.

Exercise 3: Add an author with no books. Use LEFT JOIN with GROUP BY to count books per author. Verify that the new author shows a count of 0.

Exercise 4: Try deleting an author who has books (with the default RESTRICT behavior). Verify that it fails. Then delete the author’s books first, then delete the author.

One-to-many covers most relationships you will run into. But what about cases where both sides can have many? In the next lesson, we will add a tags system to our schema through another migration, and that is where things get interesting.