My report shows reviews of a product we don’t sell

See the problem

The store adds product reviews in a hurry. The table gets the columns it needs, and nothing else:

reviews, as it was made

CREATE TABLE reviews (
  review_id   integer,
  product_id  integer,
  customer_id integer,
  stars       integer,
  body        text
);

A month later, the “top rated” page puts a mystery product first. Here are some rows of reviews:

review_id	product_id	customer_id	stars	body
1	7	2	5	Great sound
1	7	2	5	Great sound
2	80	3	5	Big and light
3	7	2	5	Still great
4	3	99	1	Broke in a week

• Review 1 is there twice: a double-click sent the form twice.
• Review 2 is for product 80. The store sells products 1 to 8: an import script typed 80 for 8.
• Review 3 is Ben (customer 2) rating the Headphones again, so Ben’s five stars count three times.
• Review 4 is by customer 99, a test account someone deleted.

The app checks every review before it saves it. So how did these get in?

Define the task

Write CREATE TABLE reviews with five columns: review_id, product_id, customer_id and stars are integer, and body is text. Add rules so that the database itself:

• rejects two reviews with the same review_id;
• rejects a review with no product_id or no customer_id;
• rejects a product_id that isn’t in products, and a customer_id that isn’t in customers;
• rejects a second review by the same customer of the same product;
• rejects deleting a product that has reviews;
• and still lets in many reviews by one customer, and many reviews of one product.

Pause and think

⏸ Pause and think

The app checks that product 80 exists before it saves a review. Name two ways a review of product 80 can still get in.

Give it two minutes with pen and paper before you open a hint.

Hints open one at a time:

Hint 1 of 3

Think of everything that writes to the table: the web form, but also import scripts, other services, and a person fixing data by hand. And two clicks that arrive at the same moment.

Hint 2 of 3

A rule in the table holds for every one of them, every time. Which column names a review? Which columns point at rows in other tables?

Hint 3 of 3

PRIMARY KEY names each row. REFERENCES products makes product_id match a row in products. UNIQUE (customer_id, product_id) allows one review for each pair.

Understand the idea

A table is a set of rows with the same columns. Each column has a name and a type, like integer, text, date or numeric(10, 2). The type already stops some mistakes: 'five' in an integer column is rejected. Each row is one thing: one customer, one order, one review. Rows have no order of their own; a query that needs one says ORDER BY.

A primary key names each row. In customers, customer_id is the primary key: no two rows share a value, and it’s never NULL. That’s how another table points at one customer. Postgres also builds an index for it, so finding customer 2 is fast.

• Pick a key that never changes. Emails change, and two people can share a name. So most tables use a made-up number, like customer_id. A code that truly never changes, like the country code IN, can be the key itself.
• Let the database number new rows: review_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY. The checks on this page give their own ids, so the editors use plain integer.
• A key can be two columns together. order_items has PRIMARY KEY (order_id, product_id): one row for each product in each order.

A foreign key points at a row in another table. In orders, customer_id integer REFERENCES customers means every order’s customer_id must be a customer_id in customers. REFERENCES customers with no column named means its primary key. The database checks it on every INSERT and UPDATE of an order, and on every DELETE from customers.

Which side gets the foreign key?

Link	Example	Where the key goes
One-to-many	a customer has many orders	on the “many” side: orders.customer_id
Many-to-many	an order has many products; a product is in many orders	a table in between, one row per pair: order_items (order_id, product_id)
One-to-one	a customer has at most one loyalty card	cards.customer_id, UNIQUE as well

A customer row can’t hold a list of orders, so the many side points back at the one.

Why not one big sheet? Say every order row held the customer’s name and email. Changing Asha’s email means changing every one of Asha’s orders, and missing one leaves two emails for one person. A customer with no orders has nowhere to live. And deleting a customer’s only order deletes the customer too. Store each fact once, and point at it with a key.

The database checks every new row against the rules. Postgres checks NOT NULL first, then the primary key and UNIQUE as it adds the row, then each foreign key at the end of the statement. The first rule that says no rejects the row, and the whole INSERT fails. Watch six rows try to go in, then try your own:

Rows checked against the keys

⤢ Full view

1. PRIMARY KEY
2. UNIQUE (customer_id, product_id)
3. REFERENCES products
4. REFERENCES customers

products

product_id	name
1	Notebook
2	Gel pen (5 pack)
3	Desk lamp
4	Water bottle
5	Wireless mouse
6	USB-C cable
7	Headphones
8	Backpack

customers

customer_id	name
1	Asha Rao
2	Ben Okafor
3	Chen Wei
4	Dana Cohen
5	Elif Demir
6	Farid Haddad
7	Grace Kim
8	Hugo Silva

reviews

review_id	product_id	customer_id

Step 1 of 15. reviews starts empty. Each new row must pass 4 rules before it goes in, in the order listed above. The first rule that says no rejects the row.

◀ BackPlayNext ▶Reset Ask me first Show as text

1CREATE TABLE reviews (
2  review_id   integer PRIMARY KEY,
3  product_id  integer NOT NULL REFERENCES products,
4  customer_id integer NOT NULL REFERENCES customers,
5  stars       integer NOT NULL,
6  body        text,
7  UNIQUE (customer_id, product_id)
8);

Try your own input

Reviews: review_id, product_id, customer_id1, 7, 2 2, 8, 3 2, 4, 5 3, 7, 2 4, 80, 2 5, 3, 9Show it

You choose what happens when a row others point at is deleted. It goes on the foreign key:

• REFERENCES products (the default, NO ACTION): deleting a product that still has reviews is rejected. ON DELETE RESTRICT is almost the same.
• ON DELETE CASCADE: deleting the product deletes its reviews too.
• ON DELETE SET NULL: the reviews stay, with product_id set to NULL (so the column must allow NULL).

★A rule in the table beats a check in the app

An app’s check runs only in the app. Import scripts, other services and a person typing SQL skip it. And two requests at the same moment can both check “no review yet”, then both save one. A key in the table is checked inside each write, for every writer, so neither can happen.

Climb the ladder

All three make a reviews table. What changes is which bad rows still get in.

Okay

Columns and types only

The idea: Name the columns and their types, and nothing else.

Quick to write, and the types already stop text in a number column.

CREATE TABLE reviews (
  review_id   integer,
  product_id  integer,
  customer_id integer,
  stars       integer,
  body        text
);

Stops

Wrong types, like 'five' for stars

Still lets in

Every bad row above

Why

A table with no rules takes any row whose values fit the types.

Where it breaks: every row from “See the problem” goes in. Okay passes 2 of the 8 checks below, the two where every row is good.

Better

A primary key, and NOT NULL

The idea: review_id names each review, and every review must give its product, customer and stars.

The gap in Okay: nothing named a review, so the double-click saved review 1 twice.

Now the second copy is rejected: ERROR: duplicate key value violates unique constraint "reviews_pkey". And a review with no product is rejected by NOT NULL.

Highlighted lines are new in this rung.

CREATE TABLE reviews (
  review_id   integer PRIMARY KEY,
  product_id  integer NOT NULL,
  customer_id integer NOT NULL,
  stars       integer NOT NULL,
  body        text
);

Stops

A repeated review_id, a missing valuewas Wrong types

Still lets in

Products and customers that don’t exist; a second review of the same product

Why

PRIMARY KEY is UNIQUE and NOT NULL together, but nothing links product_id to products.

What changed from the previous rung

CREATE TABLE reviews (
  review_id   integer,
  product_id  integer,
  customer_id integer,
  stars       integer,
  review_id   integer PRIMARY KEY,
  product_id  integer NOT NULL,
  customer_id integer NOT NULL,
  stars       integer NOT NULL,
  body        text
);

Where it breaks: product_id is still just a number. Product 80 goes in, and so does Ben’s second review of the Headphones, under a new review_id. Better passes 4 of 8 checks.

Best

Foreign keys and UNIQUE

The idea: REFERENCES links product_id to products and customer_id to customers. UNIQUE (customer_id, product_id) allows one review per customer for each product.

The gap in Better: nothing tied the numbers to real rows.

REFERENCES products rejects product 80, and REFERENCES customers rejects customer 99. UNIQUE (customer_id, product_id) rejects Ben’s second review. Deleting a product that has reviews is rejected too, because the foreign key’s default is NO ACTION. Keep NOT NULL: a foreign key lets NULL through.

Highlighted lines are new in this rung.

CREATE TABLE reviews (
  review_id   integer PRIMARY KEY,
  product_id  integer NOT NULL REFERENCES products,
  customer_id integer NOT NULL REFERENCES customers,
  stars       integer NOT NULL,
  body        text,
  UNIQUE (customer_id, product_id)
);

Stops

Every bad row above, and deleting a reviewed productwas A repeated review_id, a missing value

Still lets in

Nothing the checks try

Why

Postgres checks each rule on every INSERT, UPDATE and DELETE, whoever sends it.

What changed from the previous rung

CREATE TABLE reviews (
  review_id   integer PRIMARY KEY,
  product_id  integer NOT NULL,
  customer_id integer NOT NULL,
  product_id  integer NOT NULL REFERENCES products,
  customer_id integer NOT NULL REFERENCES customers,
  stars       integer NOT NULL,
  body        text
  body        text,
  UNIQUE (customer_id, product_id)
);

When Best isn’t best: each rule costs a little on every write: a foreign key looks up the other table, and UNIQUE keeps an index. That’s almost always worth it. Some huge tables written in bulk, like click logs, leave out foreign keys and check the data in a nightly job instead. And if a product’s reviews should go with it, add ON DELETE CASCADE, knowing that deleting a product then quietly deletes customers’ reviews.

Write it yourself

Now write it in the page. Press Run query. Each check runs your SQL on a fresh copy of the store’s data, then its own INSERT or DELETE after yours. Then it compares what your table lets in with what it should. Postgres runs in your browser, so nothing is sent anywhere.

Write only the CREATE TABLE: the checks add their own rows. Try Okay from the ladder first, and open a failed check to see what got in.

Add the rules to reviews

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)
• products (product_id, name, category, price)

-- Add rules, so the database itself rejects bad reviews. CREATE TABLE reviews ( review_id integer, product_id integer, customer_id integer, stars integer, body text );

Run queryReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: A review goes in, and reads back
   What this check runs after yours

   INSERT INTO reviews (review_id, product_id, customer_id, stars, body)
   VALUES (1, 7, 2, 5, 'Great sound');
   SELECT review_id, product_id, customer_id, stars FROM reviews;

2. ○Not run yet: A customer can review many products, and a product can have many reviews
   What this check runs after yours

   INSERT INTO reviews (review_id, product_id, customer_id, stars) VALUES
     (1, 7, 2, 5),
     (2, 8, 2, 4),
     (3, 7, 3, 3);
   SELECT review_id, product_id, customer_id, stars FROM reviews;

3. ○Not run yet: Two reviews can’t share a review_id
   What this check runs after yours

   INSERT INTO reviews (review_id, product_id, customer_id, stars) VALUES
     (1, 7, 2, 5),
     (1, 8, 3, 4);
   SELECT review_id, product_id, customer_id, stars FROM reviews;

4. ○Not run yet: A review must name its product (product_id can’t be NULL)
   What this check runs after yours

   INSERT INTO reviews (review_id, customer_id, stars)
   VALUES (1, 2, 5);
   SELECT review_id, product_id, customer_id, stars FROM reviews;

5. ○Not run yet: A review of product 80, which doesn’t exist, is rejected
   What this check runs after yours

   INSERT INTO reviews (review_id, product_id, customer_id, stars) VALUES (1, 80, 2, 5);
   SELECT review_id, product_id, customer_id, stars FROM reviews;

6. ○Not run yet: A review by customer 99, who doesn’t exist, is rejected
   What this check runs after yours

   INSERT INTO reviews (review_id, product_id, customer_id, stars) VALUES (1, 7, 99, 5);
   SELECT review_id, product_id, customer_id, stars FROM reviews;

7. ○Not run yet: A customer can review each product only once
   What this check runs after yours

   INSERT INTO reviews (review_id, product_id, customer_id, stars) VALUES
     (1, 7, 2, 5),
     (2, 7, 2, 1);
   SELECT review_id, product_id, customer_id, stars FROM reviews;

8. ○Not run yet: A product that has reviews can’t be deleted
   What this check changes

   INSERT INTO products (product_id, name, category, price)
   VALUES (9, 'Phone stand', 'electronics', 7.50);

   What this check runs after yours

   INSERT INTO reviews (review_id, product_id, customer_id, stars) VALUES (1, 9, 2, 4);
   DELETE FROM products WHERE product_id = 9;
   SELECT review_id, product_id, customer_id, stars FROM reviews;

Each check runs your SQL on its own copy of the data, then its own SQL after yours, to see what your tables let in. Nothing is kept.

Compare

Way	Stops	Still lets in	Use it when
Okay: columns and types	Wrong types	Everything else	A scratch table you’ll drop today
Better: a primary key and NOT NULL	A repeated key, missing values	Rows that point at nothing; repeat reviews	A table that points at no other table
Best: foreign keys and UNIQUE too	Every bad row above	Nothing the checks try	Almost always

Common mistakes and edge cases

!A foreign key lets NULL through

REFERENCES products checks only values that are there. A review with product_id NULL passes it. Add NOT NULL when every row must point somewhere.

• UNIQUE counts two NULLs as different. UNIQUE (parent_id, name) lets in two rows of (NULL, 'Sale'). Practice problem 2 meets this.
• NOT NULL lets in empty text. '' isn’t NULL, so city text NOT NULL takes ''. Add CHECK (city <> '') when blank text is wrong too.
• A foreign key must point at a primary key or UNIQUE column. REFERENCES customers (city) fails: there is no unique constraint matching given keys for referenced table "customers".
• Make the table you point at first. reviews needs products to exist. And DROP TABLE products fails while another table points at it.
• Adding a key to a table that already has bad rows fails. ALTER TABLE reviews ADD PRIMARY KEY (review_id) fails while review 1 is there twice: could not create unique index "reviews_pkey". Clean the rows first.
• A foreign key doesn’t index its own column. Postgres indexes products.product_id, but not reviews.product_id. Deleting a product, or listing a product’s reviews, then reads all of reviews. On a big table, add CREATE INDEX ON reviews (product_id).
• Ids from GENERATED ... AS IDENTITY have gaps. A failed or undone INSERT still uses up its number. Never count rows by the biggest id.

Say it in the interview

Questions to ask first

• Can a customer review a product more than once, or do they edit their review instead?
• When a product is removed, should that be blocked, delete its reviews, or keep them?
• Do reviews come only from the app, or also from imports and other services?
• How big will the table get, and how is it read: by product, by customer, or both?

A 60-second walkthrough

“I’d give reviews a made-up primary key, review_id, generated by the database. product_id and customer_id are foreign keys to products and customers, and NOT NULL, since every review is about one product by one customer. A UNIQUE constraint on customer_id and product_id allows one review per customer for each product. The default on delete blocks deleting a product that has reviews; I’d check that’s what we want rather than CASCADE. I’d also index product_id, because the product page reads reviews by product. Rules in the database hold for every writer, and two requests can’t race past them, unlike a check in the app.”

Likely follow-ups

• Primary key or UNIQUE? A table has one primary key, never NULL. It can have many UNIQUE constraints, and their columns may hold NULL.
• A made-up id or a real value as the key? A made-up id for things whose details change, like people. A real value for codes that never change, like country codes.
• Why not check in the app? Other writers skip it, and two requests can both pass the check and then both save. The database checks inside the write.
• What does ON DELETE CASCADE do? Deleting a row deletes the rows that point at it.

Practice

Three problems, from easy to medium. Each has hints, checks you can run here, and its own Okay, Better and Best answers. Try before you open them.

1. One wishlist row per product (easy)

Customers can save products to a wishlist. Write CREATE TABLE wishlist with customer_id and product_id (integer) and added_on (date). A customer can save many products, and a product can be saved by many customers, but a customer can save the same product only once. The customer and the product must exist.

Hint 1 of 3

Which column, or columns, name one wishlist row? Not customer_id alone: one customer saves many products.

Hint 2 of 3

A primary key can be two columns together: PRIMARY KEY (customer_id, product_id), like order_items.

Hint 3 of 3

Then link each column to its table with REFERENCES.

One wishlist row per product

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)
• products (product_id, name, category, price)

CREATE TABLE wishlist ( customer_id integer, product_id integer, added_on date NOT NULL );

Run queryReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: A saved product goes in, and reads back
   What this check runs after yours

   INSERT INTO wishlist (customer_id, product_id, added_on) VALUES (2, 7, '2026-11-01');
   SELECT customer_id, product_id, added_on FROM wishlist;

2. ○Not run yet: A customer can save two products
   What this check runs after yours

   INSERT INTO wishlist (customer_id, product_id, added_on) VALUES
     (2, 7, '2026-11-01'),
     (2, 8, '2026-11-02');
   SELECT customer_id, product_id, added_on FROM wishlist;

3. ○Not run yet: Two customers can save the same product
   What this check runs after yours

   INSERT INTO wishlist (customer_id, product_id, added_on) VALUES
     (2, 7, '2026-11-01'),
     (3, 7, '2026-11-02');
   SELECT customer_id, product_id, added_on FROM wishlist;

4. ○Not run yet: Saving the same product twice is rejected
   What this check runs after yours

   INSERT INTO wishlist (customer_id, product_id, added_on) VALUES
     (2, 7, '2026-11-01'),
     (2, 7, '2026-11-03');
   SELECT customer_id, product_id, added_on FROM wishlist;

5. ○Not run yet: Saving product 80, which doesn’t exist, is rejected
   What this check runs after yours

   INSERT INTO wishlist (customer_id, product_id, added_on) VALUES (2, 80, '2026-11-01');
   SELECT customer_id, product_id, added_on FROM wishlist;

6. ○Not run yet: A wishlist for customer 99, who doesn’t exist, is rejected
   What this check runs after yours

   INSERT INTO wishlist (customer_id, product_id, added_on) VALUES (99, 7, '2026-11-01');
   SELECT customer_id, product_id, added_on FROM wishlist;

Each check runs your SQL on its own copy of the data, then its own SQL after yours, to see what your tables let in. Nothing is kept.

Show the Okay, Better and Best answers

Okay

customer_id as the key

The idea: Make customer_id the primary key.

It rejects a repeat, but because the customer repeats, not the pair. Okay passes 3 of 6.

CREATE TABLE wishlist (
  customer_id integer PRIMARY KEY,
  product_id  integer NOT NULL,
  added_on    date NOT NULL
);

Stops

The same product twice, but only by luck

Wrongly stops

A customer’s second product

Why

The key says one row per customer, so each customer can save one product.

Better

Both columns as the key

The idea: PRIMARY KEY (customer_id, product_id): each pair at most once.

The gap in Okay: the key was one column too small.

Highlighted lines are new in this rung.

CREATE TABLE wishlist (
  customer_id integer,
  product_id  integer,
  added_on    date NOT NULL,
  PRIMARY KEY (customer_id, product_id)
);

Stops

The same pair twice

Still lets in

Products and customers that don’t exist

Why

The pair is unique, but neither column is tied to a real row.

What changed from the previous rung

CREATE TABLE wishlist (
  customer_id integer PRIMARY KEY,
  product_id  integer NOT NULL,
  added_on    date NOT NULL
  customer_id integer,
  product_id  integer,
  added_on    date NOT NULL,
  PRIMARY KEY (customer_id, product_id)
);

Where it breaks: product 80 and customer 99 go in. Better passes 4 of 6.

Best

A two-column key, and two foreign keys

The idea: The pair is the primary key, and each column REFERENCES its table.

The gap in Better: nothing tied the numbers to real rows.

Highlighted lines are new in this rung.

CREATE TABLE wishlist (
  customer_id integer REFERENCES customers,
  product_id  integer REFERENCES products,
  added_on    date NOT NULL,
  PRIMARY KEY (customer_id, product_id)
);

Stops

Repeats, and rows that point at nothing

Still lets in

Nothing the checks try

Why

A primary key column is NOT NULL already, so the foreign keys never see NULL.

What changed from the previous rung

CREATE TABLE wishlist (
  customer_id integer,
  product_id  integer,
  customer_id integer REFERENCES customers,
  product_id  integer REFERENCES products,
  added_on    date NOT NULL,
  PRIMARY KEY (customer_id, product_id)
);

When Best isn’t best: a customer may save the same product twice, say with a note each time. Then give each row its own wish_id key instead of the pair.

2. Categories inside categories (medium)

Products sit in categories, and a category can sit inside another: Cables inside Electronics. Write CREATE TABLE categories with category_id (integer), name (text) and parent_id (integer, NULL for a top-level category). Each category_id is used once. A parent must exist, and a category with sub-categories can’t be deleted. Under one parent, two sub-categories can’t share a name, but Electronics and Home can each have a Cables.

Hint 1 of 3

A foreign key can point at its own table: parent_id integer REFERENCES categories.

Hint 2 of 3

UNIQUE on name alone stops Cables under two different parents. Which pair of columns should be unique?

Hint 3 of 3

UNIQUE (parent_id, name). Keep parent_id free of NOT NULL: top-level categories have none.

Categories inside categories

Ctrl + Enter runs

CREATE TABLE categories ( category_id integer PRIMARY KEY, name text NOT NULL, parent_id integer );

Run queryReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: A top-level category has no parent (NULL)
   What this check runs after yours

   INSERT INTO categories (category_id, name, parent_id) VALUES (1, 'Electronics', NULL);
   SELECT category_id, name, parent_id FROM categories;

2. ○Not run yet: A sub-category points at its parent
   What this check runs after yours

   INSERT INTO categories (category_id, name, parent_id) VALUES
     (1, 'Electronics', NULL),
     (2, 'Cables', 1);
   SELECT category_id, name, parent_id FROM categories;

3. ○Not run yet: A parent that doesn’t exist is rejected
   What this check runs after yours

   INSERT INTO categories (category_id, name, parent_id) VALUES (2, 'Cables', 99);
   SELECT category_id, name, parent_id FROM categories;

4. ○Not run yet: Two categories can’t share a category_id
   What this check runs after yours

   INSERT INTO categories (category_id, name, parent_id) VALUES
     (1, 'Electronics', NULL),
     (1, 'Home', NULL);
   SELECT category_id, name, parent_id FROM categories;

5. ○Not run yet: A category that has sub-categories can’t be deleted
   What this check runs after yours

   INSERT INTO categories (category_id, name, parent_id) VALUES
     (1, 'Electronics', NULL),
     (2, 'Cables', 1);
   DELETE FROM categories WHERE category_id = 1;
   SELECT category_id, name, parent_id FROM categories;

6. ○Not run yet: Two parents can each have a sub-category called Cables
   What this check runs after yours

   INSERT INTO categories (category_id, name, parent_id) VALUES
     (1, 'Electronics', NULL),
     (2, 'Home', NULL),
     (3, 'Cables', 1),
     (4, 'Cables', 2);
   SELECT category_id, name, parent_id FROM categories;

7. ○Not run yet: One parent can’t have two sub-categories with the same name
   What this check runs after yours

   INSERT INTO categories (category_id, name, parent_id) VALUES
     (1, 'Electronics', NULL),
     (2, 'Cables', 1),
     (3, 'Cables', 1);
   SELECT category_id, name, parent_id FROM categories;

Each check runs your SQL on its own copy of the data, then its own SQL after yours, to see what your tables let in. Nothing is kept.

Show the Okay, Better and Best answers

Okay

Unique names, a plain parent_id

The idea: name is UNIQUE, and parent_id is just a number.

Okay passes 4 of 7: a parent of 99 goes in, deleting Electronics leaves Cables pointing at nothing, and a second Cables under Home is rejected.

CREATE TABLE categories (
  category_id integer PRIMARY KEY,
  name        text NOT NULL UNIQUE,
  parent_id   integer
);

Stops

Repeated ids and names

Gets wrong

Missing parents, deleted parents, Cables under two parents

Why

Nothing ties parent_id to a category, and UNIQUE (name) is too strict.

Better

parent_id references its own table

The idea: parent_id integer REFERENCES categories.

The gap in Okay: a parent could be any number.

A top-level category’s NULL passes the foreign key, as it should.

Highlighted lines are new in this rung.

CREATE TABLE categories (
  category_id integer PRIMARY KEY,
  name        text NOT NULL UNIQUE,
  parent_id   integer REFERENCES categories
);

Stops

Missing parents, and deleting a parent

Gets wrong

Cables under two parents

Why

The foreign key checks parents, but names are still unique across the whole table.

What changed from the previous rung

CREATE TABLE categories (
  category_id integer PRIMARY KEY,
  name        text NOT NULL UNIQUE,
  parent_id   integer
  parent_id   integer REFERENCES categories
);

Where it breaks: UNIQUE (name) rejects a second Cables, even under another parent. Better passes 6 of 7.

Best

Names unique under each parent

The idea: UNIQUE (parent_id, name) in place of UNIQUE (name).

The gap in Better: the unique rule was on the wrong columns.

Highlighted lines are new in this rung.

CREATE TABLE categories (
  category_id integer PRIMARY KEY,
  name        text NOT NULL,
  parent_id   integer REFERENCES categories,
  UNIQUE (parent_id, name)
);

Stops

Repeats under one parent, missing and deleted parents

Still lets in

Two top-level categories with the same name

Why

The pair must be unique, so Electronics and Home can each have a Cables.

What changed from the previous rung

CREATE TABLE categories (
  category_id integer PRIMARY KEY,
  name        text NOT NULL UNIQUE,
  parent_id   integer REFERENCES categories
  name        text NOT NULL,
  parent_id   integer REFERENCES categories,
  UNIQUE (parent_id, name)
);

When Best isn’t best: UNIQUE counts two NULLs as different, so two top-level categories called Sale both go in. On Postgres 15 or later, UNIQUE NULLS NOT DISTINCT (parent_id, name) stops that too.

3. Addresses go when the customer goes (medium)

Customers save delivery addresses. Write CREATE TABLE addresses with address_id and customer_id (integer), and line and city (text, never missing). A customer can have many addresses. An address must belong to a customer who exists, also after it’s changed. And when a customer is deleted, their addresses go too.

Hint 1 of 3

REFERENCES customers checks both INSERT and UPDATE of customer_id.

Hint 2 of 3

By default, deleting a customer who still has addresses is rejected. The foreign key can say what to do instead.

Hint 3 of 3

ON DELETE CASCADE deletes the rows that point at the deleted row.

Addresses go when the customer goes

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)

CREATE TABLE addresses ( address_id integer PRIMARY KEY, customer_id integer NOT NULL, line text NOT NULL, city text NOT NULL );

Run queryReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: An address goes in, and reads back
   What this check runs after yours

   INSERT INTO addresses (address_id, customer_id, line, city) VALUES (1, 2, '12 Marina Road', 'Lagos');
   SELECT address_id, customer_id, line, city FROM addresses;

2. ○Not run yet: A customer can have two addresses
   What this check runs after yours

   INSERT INTO addresses (address_id, customer_id, line, city) VALUES
     (1, 2, '12 Marina Road', 'Lagos'),
     (2, 2, '4 Allen Avenue', 'Ikeja');
   SELECT address_id, customer_id, line, city FROM addresses;

3. ○Not run yet: An address for customer 99, who doesn’t exist, is rejected
   What this check runs after yours

   INSERT INTO addresses (address_id, customer_id, line, city) VALUES (1, 99, '1 Nowhere Street', 'Pune');
   SELECT address_id, customer_id, line, city FROM addresses;

4. ○Not run yet: An address can’t be moved to a customer who doesn’t exist
   What this check runs after yours

   INSERT INTO addresses (address_id, customer_id, line, city) VALUES (1, 2, '12 Marina Road', 'Lagos');
   UPDATE addresses SET customer_id = 99 WHERE address_id = 1;
   SELECT address_id, customer_id, line, city FROM addresses;

5. ○Not run yet: Deleting Ivy (customer 9) deletes their addresses, and no one else’s
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Ivy Nair', 'ivy@example.com', 'Kochi', '2026-08-01');

   What this check runs after yours

   INSERT INTO addresses (address_id, customer_id, line, city) VALUES
     (1, 9, '7 Beach Road', 'Kochi'),
     (2, 9, '22 MG Road', 'Kochi'),
     (3, 2, '12 Marina Road', 'Lagos');
   DELETE FROM customers WHERE customer_id = 9;
   SELECT address_id, customer_id, line, city FROM addresses;

Each check runs your SQL on its own copy of the data, then its own SQL after yours, to see what your tables let in. Nothing is kept.

Show the Okay, Better and Best answers

Okay

No link to customers

The idea: customer_id is a plain number.

Okay passes 2 of 5. Deleting Ivy leaves Ivy’s two addresses pointing at nobody.

CREATE TABLE addresses (
  address_id  integer PRIMARY KEY,
  customer_id integer NOT NULL,
  line        text NOT NULL,
  city        text NOT NULL
);

Stops

Repeated ids, missing values

Gets wrong

Customer 99, and addresses left behind by a deleted customer

Why

Nothing ties customer_id to a customer.

Better

REFERENCES customers

The idea: customer_id must be a customer, on INSERT and on UPDATE.

The gap in Okay: any number was a customer.

Highlighted lines are new in this rung.

CREATE TABLE addresses (
  address_id  integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers,
  line        text NOT NULL,
  city        text NOT NULL
);

Stops

Customer 99, on INSERT and UPDATE

Gets wrong

Deleting a customer who has addresses

Why

The default, NO ACTION, rejects the DELETE while addresses point at the customer.

What changed from the previous rung

CREATE TABLE addresses (
  address_id  integer PRIMARY KEY,
  customer_id integer NOT NULL,
  customer_id integer NOT NULL REFERENCES customers,
  line        text NOT NULL,
  city        text NOT NULL
);

Where it breaks: deleting Ivy is rejected, because Ivy’s addresses still point at that row. Better passes 4 of 5.

Best

ON DELETE CASCADE

The idea: Deleting a customer deletes their addresses in the same statement.

The gap in Better: it said no where the task wanted the addresses gone.

Highlighted lines are new in this rung.

CREATE TABLE addresses (
  address_id  integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers ON DELETE CASCADE,
  line        text NOT NULL,
  city        text NOT NULL
);

Stops

Customer 99, and addresses left behind

Still lets in

Nothing the checks try

Why

CASCADE follows the foreign key and deletes the rows that point at the deleted one.

What changed from the previous rung

CREATE TABLE addresses (
  address_id  integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers,
  customer_id integer NOT NULL REFERENCES customers ON DELETE CASCADE,
  line        text NOT NULL,
  city        text NOT NULL
);

When Best isn’t best: never cascade from customers to orders. Deleting a customer would delete their order history, which the store must keep for its accounts. Keep the default there, or mark the customer as closed instead of deleting the row.

Check yourself

A few quick questions. Ones you miss are remembered, so you can find your weak spots later.

1. Predict the outputreviews has review_id integer PRIMARY KEY and holds one row, review 1. What happens?

   INSERT INTO reviews (review_id, product_id, customer_id, stars)
   VALUES (2, 7, 3, 4), (1, 8, 3, 5);

   Both rows go inReview 2 goes in, and review 1 is rejectedNeither goes in: the INSERT fails with a duplicate key errorReview 1 is replaced by the new one

   Check answer
2. Multiple choiceA customer has many orders. Where does the foreign key go?

   customers.order_id REFERENCES ordersorders.customer_id REFERENCES customersBoth tables point at each otherA table in between, like order_items

   Check answer
3. Spot the bugEvery review must be about a product that exists. Which review still gets in?

   CREATE TABLE reviews (
     review_id  integer PRIMARY KEY,
     product_id integer REFERENCES products,
     stars      integer NOT NULL
   );

   A review of product 80, which doesn’t existA review with product_id NULLTwo reviews with the same review_idA review with no stars

   Check answer
4. Multiple choicereviews.product_id is REFERENCES products, with nothing after it. Product 7 has reviews. What does DELETE FROM products WHERE product_id = 7 do?

   Deletes the product and its reviewsDeletes the product and sets the reviews’ product_id to NULLFails: the reviews still point at product 7Deletes the product and leaves the reviews as they are

   Check answer
5. Multiple choiceWhy do most tables use a made-up id, like customer_id, as the primary key instead of the email?

   Numbers sort faster than textAn email can change, and every row pointing at it would have to change tooPostgres can’t make text a primary keyEmails can’t be UNIQUE

   Check answer

Flashcards

Add these to your review deck. Each card comes back a little later every time you get it right.

• Card 1What does a primary key promise?Show answer
• Card 2What does a foreign key promise?Show answer
• Card 3Where does the key go for one-to-many, and for many-to-many?Show answer
• Card 4What are the three choices for ON DELETE?Show answer
• Card 5Why put rules in the table instead of checking in the app?Show answer

Add 5 cards to my review deck

Cheat sheet

• A table is rows with the same columns, and each column has a type. Rows have no order of their own.
• PRIMARY KEY: names each row; unique and never NULL; one per table; can be two or more columns.
• REFERENCES t: the value must be a key in t. NULL passes, unless the column is NOT NULL.
• UNIQUE (a, b): no two rows share the pair. Two NULLs count as different.
• One-to-many: the key goes on the many side. Many-to-many: a table in between, with a row per pair.
• On delete: the default rejects it, CASCADE deletes the rows that point at it, SET NULL clears their key.
• Postgres checks NOT NULL, then the primary key and UNIQUE, then each foreign key. The first that says no rejects the row.

Go deeper

• In the PostgreSQL docs: constraints, CREATE TABLE and identity columns.
• Try your own rows: the keys picture on the SQL visualizer.
• Next on the SQL roadmap: the whole map, from your first SELECT to window functions.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

• Say what a table, a row, a column and a key are
• Pick a primary key, and say why most tables use a made-up id
• Link tables with foreign keys, and say which side of a one-to-many link gets the key
• Choose what happens on delete: reject it, cascade or set NULL

✓Mark as completeI already know this

Status: Not started