Skip to content
aviral gupta
Get in touch

// B4.3 · ~30 min · Beginner

Constraints: primary, foreign, unique, check

After this lesson you can give a table rules that refuse bad rows, link loans to real books and members, decide what a delete does to them, and read the error a broken rule raises.

Lesson 3 of 5 in B4 Changing data and schema

You will be able to

  • Declare NOT NULL, UNIQUE, PRIMARY KEY and CHECK, and predict which rows they refuse
  • Link tables with REFERENCES and choose what ON DELETE does: NO ACTION, RESTRICT or CASCADE
  • Read a constraint error and find the rule and the row behind it

  1. Warm-up · Activity 1 of 7

    Warm-up: two members must never share an e-mail address. Which constraint says so?

  2. Predict · Activity 2 of 7

    Predict before you read on. What happens at the second INSERT?

    CREATE TABLE members (id integer PRIMARY KEY, name text);
INSERT INTO members VALUES (1, 'Ada');
INSERT INTO members VALUES (1, 'Grace');

  3. Practice · Activity 3 of 7

    Fill in the operator so that copies may be 0 or more, but never negative.

    CREATE TABLE books (title text, copies integer CHECK (copies ____ 0));
    CREATE TABLE books (title text, copies integer CHECK (copies 0));

  4. Practice · Activity 4 of 7

    Match each constraint to the row it refuses.

  5. Practice · Activity 5 of 7

    In the library of the worked example, loans.member_id has ON DELETE CASCADE. Of the five loans, two are Grace’s (member 2). How many loans are left?

    DELETE FROM members WHERE id = 2;

  6. Brain teaser · Activity 6 of 7

    Brain teaser. loans has CHECK (returned_on >= loaned_on) and five rows. The new loan has no return date yet. What does the SELECT return?

    INSERT INTO loans VALUES (6, 4, 4, '2026-10-03', NULL);
SELECT count(*) FROM loans;

  7. Apply · Activity 7 of 7

    Mini-task. With the keyed library of the worked example, create reviews: an id that identifies each review, the book and the member (both required, and both must exist; delete a book or member and their reviews go too), and stars from 1 to 5, required. A member may review a book only once. Add two reviews, then try a third by the same member for the same book, and stars = 6, and read both errors.

    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

The library with keys and rules

main.sql builds the library of module B1 again, this time with rules: keys on every id, NOT NULL where a value is required, a UNIQUE e-mail, CHECKs on copies and dates, and foreign keys from loans to books and members. It lists the constraints of loans with the names PostgreSQL gave them, then deletes Linus and counts the loans. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f main.sql.

main.sql

-- Referenced tables first: loans refers to both.
CREATE TABLE members (
  id integer PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  city text,
  joined date NOT NULL
);

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

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');

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);

-- Every loan must point to an existing book and member.
CREATE TABLE loans (
  id integer PRIMARY KEY,
  book_id integer NOT NULL REFERENCES books (id),
  member_id integer NOT NULL REFERENCES members (id) ON DELETE CASCADE,
  loaned_on date NOT NULL,
  returned_on date,
  CHECK (returned_on >= loaned_on)
);

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);

-- The rules of loans and their names (p primary key, f foreign key, c check, n not null).
SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'loans'::regclass
ORDER BY conname;

-- ON DELETE CASCADE: Linus leaves, and his loan goes with him.
DELETE FROM members WHERE id = 3;
SELECT count(*) AS loans_left FROM loans;

Run it with

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

Output

         conname          | contype
--------------------------+---------
 loans_book_id_fkey       | f
 loans_book_id_not_null   | n
 loans_check              | c
 loans_id_not_null        | n
 loans_loaned_on_not_null | n
 loans_member_id_fkey     | f
 loans_member_id_not_null | n
 loans_pkey               | p
(8 rows)

 loans_left
------------
          4
(1 row)
  • The CHECK with two columns was named loans_check; the others carry their column name.
  • The primary key made id NOT NULL as well: loans_id_not_null.
  • Every row of the data passed every rule, so all INSERTs ran.
  • Deleting member 3 also deleted loan 4, the only loan that referred to him.
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

Give books its rules

The starter creates books with types only, so PostgreSQL would accept a book without a title or with -5 copies. Add rules in CREATE TABLE: id is the primary key, title must not be NULL, and copies must not be NULL and must be 0 or more (a CHECK). Keep the six INSERTed books.

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

    Constraints go after the type of their column: id integer PRIMARY KEY.

  2. Hint 2

    One column can have two: copies integer NOT NULL CHECK (…).

  3. Hint 3

    The condition for copies is copies >= 0, in parentheses after CHECK.

Show a solution

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

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

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);
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

-- Types only: PostgreSQL checks nothing else yet.
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);

test.sql

-- test: id is the primary key of books
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');

-- test: title is NOT NULL
SELECT attnotnull FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'title';

-- test: copies is NOT NULL
SELECT attnotnull FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'copies';

-- test: A CHECK constraint guards copies
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 = 'c' AND a.attname = 'copies');

-- test: The six books are in the table
SELECT count(*) = 6 FROM books;

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 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

Loans that point to real rows

setup.sql has created members and books with their keys. The starter creates loans with no rules, then Linus (member 3) leaves, and his loan stays behind, pointing to nobody. Give loans its rules: id is the primary key, book_id refers to books (id), member_id refers to members (id) with ON DELETE CASCADE, and a CHECK that returned_on is not before loaned_on. Then Linus’s loan goes with him.

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

    A foreign key goes after the column type: book_id integer REFERENCES books (id).

  2. Hint 2

    ON DELETE CASCADE follows the REFERENCES part of member_id.

  3. Hint 3

    A CHECK on two columns is written after the last column: , CHECK (returned_on >= loaned_on)

Show a solution

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

CREATE TABLE loans (
  id integer PRIMARY KEY,
  book_id integer REFERENCES books (id),
  member_id integer REFERENCES members (id) ON DELETE CASCADE,
  loaned_on date,
  returned_on date,
  CHECK (returned_on >= loaned_on)
);

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);

DELETE FROM members WHERE id = 3;

SELECT id, book_id, member_id FROM loans ORDER BY id;
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 without rules, then Linus leaves the library.
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);

DELETE FROM members WHERE id = 3;

SELECT id, book_id, member_id FROM loans ORDER BY id;

test.sql

-- test: id is the primary key of loans
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 = 'loans'::regclass AND c.contype = 'p' AND cardinality(c.conkey) = 1 AND a.attname = 'id');

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

-- test: member_id refers to members, with ON DELETE CASCADE
SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = 'loans'::regclass AND contype = 'f' AND confrelid = 'members'::regclass AND confdeltype = 'c');

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

-- test: Linus’s loan is gone with him, the other four remain
-- output:
--  id | book_id | member_id
-- ----+---------+-----------
--   1 |       1 |         1
--   2 |       3 |         1
--   3 |       2 |         2
--   5 |       6 |         2
-- (4 rows)

setup.sql

CREATE TABLE members (
  id integer PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  city text,
  joined date NOT NULL
);

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

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');

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);

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

A second row with the same key

INSERT INTO books VALUES (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2);

What psql prints

ERROR:  duplicate key value violates unique constraint "books_pkey"

Why, and the fix

books already has a row with id 1, and the primary key allows each id once; the DETAIL line names the key, (id)=(1). Running a data script twice is the usual cause. Give the new row an id no other row has, or, if the row is already there, leave the INSERT out or change the row with UPDATE.

A loan for a member who does not exist

INSERT INTO loans VALUES (6, 1, 9, '2026-10-03', NULL);

What psql prints

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

Why, and the fix

member_id REFERENCES members (id), and no member has the id 9 (DETAIL: Key (member_id)=(9) is not present in table "members"). Add the member first, then the loan, or use the id of an existing member. The order matters for the same reason when you load data: parents before children.

Deleting a book that is still lent

DELETE FROM books WHERE id = 1;

What psql prints

ERROR:  update or delete on table "books" violates foreign key constraint "loans_book_id_fkey" on table "loans"

Why, and the fix

Two loans still refer to Dune, and book_id has the default action, NO ACTION, so the delete is refused. Delete or change those loans first, or, if the loans should vanish with the book, declare REFERENCES books (id) ON DELETE CASCADE. RESTRICT would refuse it too, with the words violates RESTRICT setting of foreign key constraint.

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

Rules inside one table

A constraint is a rule the table checks on every INSERT and UPDATE. A row that breaks it is refused with an error, and the whole statement changes nothing. NOT NULL forbids a missing value; an empty string '' is still a value. UNIQUE forbids two rows with the same value, but two NULLs do not count as equal, so many members may have no e-mail. PRIMARY KEY marks the column that identifies a row: unique and not null together, and a table has at most one. CHECK (copies >= 0) accepts a row when the condition is true or NULL, so add NOT NULL where a missing value is wrong too.

Foreign keys link tables

book_id integer REFERENCES books (id) says that every book_id in loans must be the id of an existing book. The referenced column must be a primary key or UNIQUE, and its table must exist first. A loan with an unknown book_id is refused; a NULL book_id passes unless the column is NOT NULL. The rule guards the other side too. By default (NO ACTION) deleting a book that loans still refer to fails. ON DELETE RESTRICT refuses it as well, a little more strictly: the check cannot be put off to the end of a transaction. ON DELETE CASCADE deletes the referring rows instead: delete a member, and their loans go too.

Reading a constraint error

Every constraint has a name, and the error message names it. Without CONSTRAINT name, PostgreSQL builds one from the table and the column: books_pkey, members_email_key for UNIQUE, books_copies_check, loans_member_id_fkey, and loans_check for a CHECK written after the columns. The messages read: duplicate key value violates unique constraint "books_pkey"; null value in column "title" of relation "books" violates not-null constraint; new row for relation "books" violates check constraint "books_copies_check"; insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey". The DETAIL line under it shows the key or the failing row.

Sources

Last reviewed October 3, 2026