Skip to content
aviral gupta
Get in touch

// B4.5 · ~30 min · Beginner

Build: evolving the library schema

After this build you can put rules on a database that already holds bad data: find the rows that break them, fix them, add the rules, and prove they hold.

Lesson 5 of 5 in B4 Changing data and schema

End of the module

You will be able to

  • Find the rows that would break a new rule, and fix them with UPDATE and DELETE
  • Add keys, foreign keys, a CHECK, a NOT NULL and a default in an order that works
  • Prove the rules: predict which inserts and deletes the evolved schema now refuses

  1. Warm-up · Activity 1 of 7

    Warm-up from the last lesson. Persuasion was added with copies NULL. What happens?

    ALTER TABLE books ALTER COLUMN copies SET NOT NULL

  2. Predict · Activity 2 of 7

    Predict before you read on. The library has loan 6 for a member 9 who does not exist, loan 7 returned before it went out, and Persuasion without copies. No table has a key yet. Which of these rules can be added right now?

  3. Practice · Activity 3 of 7

    Fill in the gap so that the query lists the loans whose member does not exist.

    SELECT l.id, l.member_id
FROM loans l
LEFT JOIN members m ON m.id = l.member_id
WHERE m.id IS ____;
    SELECT l.id, l.member_id FROM loans l LEFT JOIN members m ON m.id = l.member_id WHERE m.id IS ;

  4. Practice · Activity 4 of 7

    Put the steps of the build in an order that works on the library with its bad rows.

    1. 1.Find the rows that would break the rules
    2. 2.Add the foreign keys, the CHECK and the NOT NULL
    3. 3.Fix them with UPDATE and DELETE
    4. 4.Add the primary keys
    5. 5.Try a bad insert and read the error

  5. Practice · Activity 5 of 7

    The new rule will be CHECK (returned_on >= loaned_on). SELECT id FROM loans WHERE … Which condition lists exactly the loans that would break it?

  6. Brain teaser · Activity 6 of 7

    Brain teaser. The data is fixed now, but loans 2, 4 and 5 are still open: their returned_on is NULL. What happens?

    ALTER TABLE loans ADD CHECK (returned_on >= loaned_on)

  7. Apply · Activity 7 of 7

    Mini-task. In the editor of the worked example, after the build: add Middlemarch by George Eliot as book 8 without copies, and show its copies. Then, as a statement of its own, try a loan 8 of Dune for member 9 and read the error. Which constraint does it name?

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

From bad rows to enforced rules

setup.sql loads the library of module B1 without any rules, plus three rows that got in: a loan for member 9, a loan with swapped dates and a book without copies. main.sql finds them, fixes them, adds keys, foreign keys, a CHECK, a NOT NULL, a default and a new column, and lists what loans now enforces. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Find the rows that would break the new rules.
SELECT l.id, l.member_id
FROM loans l
LEFT JOIN members m ON m.id = l.member_id
WHERE m.id IS NULL;

SELECT id, loaned_on, returned_on FROM loans WHERE returned_on < loaned_on;

SELECT id, title FROM books WHERE copies IS NULL;

-- 2. Fix them.
DELETE FROM loans WHERE id = 6;
UPDATE loans SET loaned_on = '2026-09-15', returned_on = '2026-09-25' WHERE id = 7;
UPDATE books SET copies = 1 WHERE id = 7;

-- 3. Keys first, then the foreign keys and rules that rely on them.
ALTER TABLE books ADD PRIMARY KEY (id);
ALTER TABLE members ADD PRIMARY KEY (id);
ALTER TABLE loans ADD PRIMARY KEY (id);
ALTER TABLE loans ADD FOREIGN KEY (book_id) REFERENCES books (id);
ALTER TABLE loans ADD FOREIGN KEY (member_id) REFERENCES members (id);
ALTER TABLE loans ADD CHECK (returned_on >= loaned_on);
ALTER TABLE books ALTER COLUMN copies SET NOT NULL;
ALTER TABLE books ALTER COLUMN copies SET DEFAULT 1;

-- 4. A new column for members.
ALTER TABLE members ADD COLUMN active boolean NOT NULL DEFAULT true;

-- 5. What loans now enforces.
SELECT contype, count(*) FROM pg_constraint
WHERE conrelid = 'loans'::regclass
GROUP BY contype
ORDER BY contype;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

-- Rows that got in while nothing was checked.
INSERT INTO books VALUES (7, 'Persuasion', 'Jane Austen', 1817, 'classic', NULL);
INSERT INTO loans VALUES
  (6, 5, 9, '2026-09-20', NULL),
  (7, 4, 4, '2026-09-25', '2026-09-15');

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

 id | member_id
----+-----------
  6 |         9
(1 row)

 id | loaned_on  | returned_on
----+------------+-------------
  7 | 2026-09-25 | 2026-09-15
(1 row)

 id |   title
----+------------
  7 | Persuasion
(1 row)

 contype | count
---------+-------
 c       |     1
 f       |     2
 n       |     1
 p       |     1
(4 rows)
  • The three queries are the rules turned around: each lists the rows its rule would refuse.
  • Loan 6 is deleted because nobody can say whose it was; loan 7 keeps both dates, now in order; Persuasion gets 1 copy.
  • The primary keys come before the foreign keys: a foreign key needs a primary key or UNIQUE column to refer to.
  • pg_constraint lists the rules of loans by type: c is the CHECK, f the two foreign keys, p the primary key, and n the NOT NULL that the primary key put on id.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

Step 1: find and fix the bad rows

The starter finds the three bad rows. Fix them: loan 6 cannot be traced to a member, so remove it; loan 7 went out on 2026-09-15 and came back on 2026-09-25, the dates were swapped; Persuasion has 1 copy. Keep every other row.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    DELETE FROM loans WHERE id = 6; removes exactly the loan for member 9.

  2. Hint 2

    For loan 7, set both dates: UPDATE loans SET loaned_on = '2026-09-15', returned_on = '2026-09-25' WHERE id = 7;

  3. Hint 3

    Persuasion is book 7: UPDATE books SET copies = 1 WHERE id = 7;

Show a solution

One way to solve it. Yours can look different and still pass the checks.

DELETE FROM loans WHERE id = 6;
UPDATE loans SET loaned_on = '2026-09-15', returned_on = '2026-09-25' WHERE id = 7;
UPDATE books SET copies = 1 WHERE id = 7;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Loans whose member does not exist.
SELECT l.id, l.member_id
FROM loans l
LEFT JOIN members m ON m.id = l.member_id
WHERE m.id IS NULL;

-- Loans that came back before they went out.
SELECT id, loaned_on, returned_on FROM loans WHERE returned_on < loaned_on;

-- Books without copies.
SELECT id, title FROM books WHERE copies IS NULL;

test.sql

-- test: Every loan names an existing member
SELECT NOT EXISTS (SELECT 1 FROM loans l LEFT JOIN members m ON m.id = l.member_id WHERE m.id IS NULL);

-- test: Loan 7 went out on 2026-09-15 and came back on 2026-09-25
SELECT loaned_on = '2026-09-15' AND returned_on = '2026-09-25' FROM loans WHERE id = 7;

-- test: No loan comes back before it went out
SELECT NOT EXISTS (SELECT 1 FROM loans WHERE returned_on < loaned_on);

-- test: Persuasion has 1 copy
SELECT copies = 1 FROM books WHERE id = 7;

-- test: Six loans, seven books and four members remain
SELECT (SELECT count(*) FROM loans) = 6 AND (SELECT count(*) FROM books) = 7 AND (SELECT count(*) FROM members) = 4;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

-- Rows that got in while nothing was checked.
INSERT INTO books VALUES (7, 'Persuasion', 'Jane Austen', 1817, 'classic', NULL);
INSERT INTO loans VALUES
  (6, 5, 9, '2026-09-20', NULL),
  (7, 4, 4, '2026-09-25', '2026-09-15');

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Step 2: add the rules

The data is clean now. Add the rules with ALTER TABLE: a primary key on id in books, members and loans; foreign keys from loans.book_id to books and from loans.member_id to members; CHECK (returned_on >= loaned_on) on loans; copies NOT NULL with default 1; and a new column active in members, boolean, NOT NULL, true for everyone.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    Primary keys first: ALTER TABLE books ADD PRIMARY KEY (id); and the same for members and loans.

  2. Hint 2

    ALTER TABLE loans ADD FOREIGN KEY (member_id) REFERENCES members (id); and the same for book_id.

  3. Hint 3

    ALTER COLUMN copies SET NOT NULL and SET DEFAULT 1 are two statements; ADD COLUMN active boolean NOT NULL DEFAULT true fills the four members.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

ALTER TABLE books ADD PRIMARY KEY (id);
ALTER TABLE members ADD PRIMARY KEY (id);
ALTER TABLE loans ADD PRIMARY KEY (id);
ALTER TABLE loans ADD FOREIGN KEY (book_id) REFERENCES books (id);
ALTER TABLE loans ADD FOREIGN KEY (member_id) REFERENCES members (id);
ALTER TABLE loans ADD CHECK (returned_on >= loaned_on);
ALTER TABLE books ALTER COLUMN copies SET NOT NULL;
ALTER TABLE books ALTER COLUMN copies SET DEFAULT 1;
ALTER TABLE members ADD COLUMN active boolean NOT NULL DEFAULT true;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- The data is clean. Add the rules here, keys first.
SELECT count(*) FROM loans;

test.sql

-- test: books, members and loans have a primary key on id
SELECT EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey) WHERE c.conrelid = 'books'::regclass AND c.contype = 'p' AND cardinality(c.conkey) = 1 AND a.attname = 'id') AND EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey) WHERE c.conrelid = 'members'::regclass AND c.contype = 'p' AND cardinality(c.conkey) = 1 AND a.attname = 'id') AND EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey) WHERE c.conrelid = 'loans'::regclass AND c.contype = 'p' AND cardinality(c.conkey) = 1 AND a.attname = 'id');

-- test: loans.book_id refers to books
SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = 'loans'::regclass AND contype = 'f' AND confrelid = 'books'::regclass);

-- test: loans.member_id refers to members
SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = 'loans'::regclass AND contype = 'f' AND confrelid = 'members'::regclass);

-- test: A CHECK constraint guards the dates of loans
SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = 'loans'::regclass AND contype = 'c');

-- test: copies is NOT NULL with default 1
SELECT a.attnotnull AND pg_get_expr(d.adbin, d.adrelid) = '1' FROM pg_attribute a JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum WHERE a.attrelid = 'books'::regclass AND a.attname = 'copies';

-- test: Every member is active, and active is NOT NULL
SELECT (SELECT count(*) FROM members WHERE active) = 4 AND (SELECT attnotnull FROM pg_attribute WHERE attrelid = 'members'::regclass AND attname = 'active');

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

-- Rows that got in while nothing was checked.
INSERT INTO books VALUES (7, 'Persuasion', 'Jane Austen', 1817, 'classic', NULL);
INSERT INTO loans VALUES
  (6, 5, 9, '2026-09-20', NULL),
  (7, 4, 4, '2026-09-25', '2026-09-15');

-- Step 1: the bad rows, fixed.
DELETE FROM loans WHERE id = 6;
UPDATE loans SET loaned_on = '2026-09-15', returned_on = '2026-09-25' WHERE id = 7;
UPDATE books SET copies = 1 WHERE id = 7;

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

Adding the foreign key before the data is fixed

ALTER TABLE members ADD PRIMARY KEY (id);
ALTER TABLE loans ADD FOREIGN KEY (member_id) REFERENCES members (id);

What psql prints

ERROR:  insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey"

Why, and the fix

The new foreign key is checked against every loan, and loan 6 names member 9, who does not exist; the DETAIL line names the key. Find such rows with LEFT JOIN members … WHERE m.id IS NULL, fix or delete them, then add the foreign key.

A foreign key to a table without a key

ALTER TABLE loans ADD FOREIGN KEY (book_id) REFERENCES books (id);

What psql prints

ERROR:  there is no unique constraint matching given keys for referenced table "books"

Why, and the fix

A foreign key must refer to a primary key or UNIQUE column, so that each value finds one row. Add ALTER TABLE books ADD PRIMARY KEY (id); first.

A CHECK that the existing rows break

ALTER TABLE loans ADD CHECK (returned_on >= loaned_on);

What psql prints

ERROR:  check constraint "loans_check" of relation "loans" is violated by some row

Why, and the fix

Loan 7 came back before it went out. List such rows with WHERE returned_on < loaned_on, correct the dates with UPDATE, then add the CHECK again.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

Find the bad rows before you add a rule

A rule added with ALTER TABLE is checked against every row at once, so first ask which rows would break it. Each rule has its query. A foreign key from loans to members: the loans without a member, LEFT JOIN members m … WHERE m.id IS NULL. A CHECK (returned_on >= loaned_on): WHERE returned_on < loaned_on; open loans have NULL there and pass, both in the query and in the CHECK. A NOT NULL on copies: WHERE copies IS NULL. In the library these find loan 6, for a member 9 who does not exist, loan 7, whose dates were typed the wrong way round, and Persuasion, which has no copies.

Fix the data, then add the rules in order

Decide for each bad row what is true. Loan 6 cannot be traced to anyone, so DELETE it. Loan 7 keeps both dates, in the right order: UPDATE sets them again (SET loaned_on = returned_on, returned_on = loaned_on also works, because SET reads the old values). Persuasion gets 1 copy. Then the rules: primary keys first, because a foreign key may only refer to a primary key or UNIQUE column; members without one gives there is no unique constraint matching given keys for referenced table "members". Then the foreign keys, the CHECK, SET NOT NULL, SET DEFAULT 1, and the new column with its DEFAULT.

Prove that the rules hold

A rule you have not seen fail is a rule you hope for. After the build, try what the rules should refuse, one statement at a time: a loan for member 9 fails with violates foreign key constraint "loans_member_id_fkey"; a loan returned before it went out with violates check constraint "loans_check"; a book with copies NULL with violates not-null constraint; deleting Ada, who has loans, with is still referenced from table "loans". Then try what should work: a book without copies now gets the default 1. Count the rows as well, so you know the build kept what it should: six loans, seven books, four members.

Sources

Last reviewed October 4, 2026