Rules in the Table: CHECK and UNIQUE in Python

A table can do more than store rows: it can refuse bad ones. Rules written into CREATE TABLE hold for every insert, whether it comes from a route, a script or a person typing SQL. A row that breaks a rule is turned away with an error.

Four rules: NOT NULL refuses an empty value. DEFAULT fills in a column the insert leaves out. CHECK (…) refuses a row whose condition is false. UNIQUE refuses a second row with the same value. A rule on two columns, like a UNIQUE pair, goes on its own line after the columns. This coupons table uses all four:

CREATE TABLE coupons (
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    percent INTEGER NOT NULL CHECK (percent BETWEEN 1 AND 90),
    active INTEGER NOT NULL DEFAULT 1
)

Two details matter. BETWEEN 1 AND 90 includes both ends. And DEFAULT only fills a column that is left out, so an insert that writes NULL on purpose still breaks NOT NULL.

Your reviews table already has id, customer_id and product_id. The check creates it, tries six inserts and then shows the table. It must print:

INSERT INTO reviews (customer_id, product_id, rating) VALUES (1, 1, 5)
  OK
INSERT INTO reviews (customer_id, product_id, rating) VALUES (1, 3, 2)
  OK
INSERT INTO reviews (customer_id, product_id, rating) VALUES (1, 1, 1)
  Error: UNIQUE constraint failed: reviews.customer_id, reviews.product_id
INSERT INTO reviews (customer_id, product_id, rating) VALUES (3, 6, 9)
  Error: CHECK constraint failed: rating BETWEEN 1 AND 5
INSERT INTO reviews (customer_id, product_id, rating, status) VALUES (3, 6, 4, NULL)
  Error: NOT NULL constraint failed: reviews.status
INSERT INTO reviews (customer_id, product_id, rating) VALUES (3, 6, 4)
  OK
id | customer_id | product_id | rating | status
1 | 1 | 1 | 5 | pending
2 | 1 | 3 | 2 | pending
3 | 3 | 6 | 4 | pending
(3 rows)

Your task: finish reviews. Add a rating that must be from 1 to 5 and a status that is 'pending' unless an insert says otherwise. Also allow at most one review per customer per product.

Assignment
Follow the Coding Tutorial to add the UNIQUE pair, the rating column with its CHECK, and the status column with its DEFAULT.

Hint
Every line inside the brackets ends with a comma, except the last one: the UNIQUE line. The condition of a CHECK goes in round brackets.