Primary Keys and Constraints

A table accepts any row you give it, even one that makes no sense: two reviews with the same id, a review about no product, a 6-star rating. Constraints are rules you write into CREATE TABLE, after a column's type. SQLite checks them on every INSERT. A row that breaks one is refused with an error and never reaches the table.

There are three kinds here. PRIMARY KEY marks the column that tells rows apart: no two rows may share it, and with INTEGER PRIMARY KEY an INSERT that gives no id gets the next free number. NOT NULL refuses an empty value. CHECK takes a condition in parentheses and refuses any row that makes it false. A coupons table, for example, written the way your code writes SQL, as an array of strings joined with spaces:

const query: string = [
  "CREATE TABLE coupons (",
  "code TEXT PRIMARY KEY,",
  "percent INTEGER NOT NULL CHECK (percent BETWEEN 5 AND 50),",
  "note TEXT",
  ")",
].join(" ");

Your query already creates reviews, so far with only its text column. The check tries seven reviews. For each one it prints the INSERT, then OK or the error. Then it prints the table. With your rules in place, it prints:

INSERT INTO reviews (product_id, stars, text) VALUES (1, 5, 'Great mug')
  OK
INSERT INTO reviews (product_id, stars, text) VALUES (3, 1, 'Too dim')
  OK
INSERT INTO reviews (product_id, stars, text) VALUES (6, 6, 'Best teapot ever')
  Error: CHECK constraint failed: stars BETWEEN 1 AND 5
INSERT INTO reviews (product_id, stars, text) VALUES (5, 0, 'Died in a week')
  Error: CHECK constraint failed: stars BETWEEN 1 AND 5
INSERT INTO reviews (stars, text) VALUES (4, 'Which product?')
  Error: NOT NULL constraint failed: reviews.product_id
INSERT INTO reviews (product_id, text) VALUES (2, 'No stars')
  Error: NOT NULL constraint failed: reviews.stars
INSERT INTO reviews (id, product_id, stars, text) VALUES (1, 4, 3, 'Same id')
  Error: UNIQUE constraint failed: reviews.id
id | product_id | stars | text
1 | 1 | 5 | Great mug
2 | 3 | 1 | Too dim
(2 rows)

Your task: above the string "text TEXT", add one string for each of the columns id (an INTEGER and the PRIMARY KEY), product_id (an INTEGER that is NOT NULL) and stars (an INTEGER that is NOT NULL and must be BETWEEN 1 AND 5).

Assignment
Follow the Coding Tutorial to add the id, product_id and stars columns, each with its constraints.

Hint
Each column reads: name, type, then its rules. The CHECK condition sits in its own parentheses. Each column is one string of the array: the SQL comma that separates the columns goes inside the quotes, and the array's comma comes after them.