Unique and Partial Indexes

An index is more than a faster way to search. A unique index is also a rule: SQLite refuses any row that would put the same value into it twice. A partial index ends with a WHERE, like a query: only the rows that match it go into the index.

Together they make a rule for some rows only. For example, if the shop had a reviews table, this index would let a product have many reviews but only one pinned review:

CREATE UNIQUE INDEX idx_reviews_product_id ON reviews (product_id)
WHERE pinned = 1

The hidden setup adds a carts table to shop.db. A customer may keep many old carts, but only one open cart:

id | customer_id | status    | created
1  | 1           | ordered   | 2026-03-01
2  | 1           | ordered   | 2026-04-04
3  | 1           | open      | 2026-06-02
4  | 2           | abandoned | 2026-05-02
5  | 3           | ordered   | 2026-04-17
6  | 3           | open      | 2026-06-03

A plain unique index on customer_id is refused, because customer 1 already has three carts. Keep it to the open carts and the check's tries get these answers: a second open cart is refused, a first one is saved, and checking out (cart 3 becomes 'ordered') frees the place. SQLite only uses a partial index when the query asks for the same rows, so the query repeats the index's condition:

create_index: OK
INSERT INTO carts (customer_id, status, created) VALUES (1, 'open', '2026-06-05')
  Error: UNIQUE constraint failed: carts.customer_id
INSERT INTO carts (customer_id, status, created) VALUES (2, 'open', '2026-06-05')
  OK
UPDATE carts SET status = 'ordered' WHERE id = 3
  OK
INSERT INTO carts (customer_id, status, created) VALUES (1, 'open', '2026-06-06')
  OK
query: SEARCH carts USING INDEX idx_carts_customer_id (customer_id=?)
id | created
8 | 2026-06-06
(1 row)

Your task: in create_index, write the unique index idx_carts_customer_id on carts (customer_id), kept to the carts whose status is 'open'. In query, select the id and created of customer 1's open cart.

Assignment
Follow the Coding Tutorial to write the partial unique index and a query that can search it.

Hint
The index's WHERE comes after the column in brackets. The query needs two conditions: customer_id = 1 and the index's own status = 'open'. With only the first, the plan says SCAN carts.