When an author leaves, their books go too

Choose what a foreign key does when the row it points to is deleted.

What to build

Two linked tables, where deleting an author also deletes that author's books.

Requirements

Create authors with id, name and retired, and books with id, title and an author_id that references authors(id) and cascades on delete.
Copy the source rows in with the INSERT statements given in the starter.
Delete the retired authors, then show the books that are left.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE authors_src (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  retired INTEGER NOT NULL
);
CREATE TABLE books_src (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL
);

Output

Columns id and title of the remaining books, in order of id. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Every book's author exists in the source table. StepVioz turns foreign key checks on.

Example

When a test starts with:

INSERT INTO authors_src VALUES (1, 'Humayun', 1), (2, 'Selina', 0);
INSERT INTO books_src VALUES (1, 'Book A', 1), (2, 'Book B', 2), (3, 'Book C', 1);

your SQL should print:

id  title
--  ------
2   Book B
(1 row)

Why it works

With ON DELETE CASCADE, deleting a parent row also deletes the rows that point to it, so no book is left pointing at a missing author. Without it, the foreign key refuses the delete.

Examples

Example 1 · Input

CREATE TABLE authors_src (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  retired INTEGER NOT NULL
);
CREATE TABLE books_src (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL
);
INSERT INTO authors_src VALUES (1, 'Humayun', 1), (2, 'Selina', 0);
INSERT INTO books_src VALUES (1, 'Book A', 1), (2, 'Book B', 2), (3, 'Book C', 1);

Expected output

id  title
--  ------
2   Book B
(1 row)

Example 2 · Input

CREATE TABLE authors_src (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  retired INTEGER NOT NULL
);
CREATE TABLE books_src (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL
);
INSERT INTO authors_src VALUES (1, 'X', 0);
INSERT INTO books_src VALUES (1, 'T1', 1);

Expected output

id  title
--  -----
1   T1
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Hint 1

`author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE` deletes a book when its author is deleted.

Hint 2

Without CASCADE, the DELETE is refused, because books would point to an author who no longer exists.

Solution

Show one correct solution (try it yourself first)

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  retired INTEGER NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors SELECT id, name, retired FROM authors_src;
INSERT INTO books SELECT id, title, author_id FROM books_src;

DELETE FROM authors WHERE retired = 1;

SELECT id, title FROM books ORDER BY id;

Lesson: Keys and constraints →