Lesson 11 of 12 · 12 min

Designing tables

So far you have used tables someone else designed. CREATE TABLE makes your own:

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  phone TEXT,
  rating INTEGER CHECK (rating BETWEEN 1 AND 5)
);

Each column has a name and a type. The common SQLite types are INTEGER, REAL (decimal numbers), TEXT and BLOB (raw bytes).

Constraints

Constraints are rules the database enforces for you, so bad data cannot get in no matter which program writes it:

Constraint Means
PRIMARY KEY unique id for each row (filled in automatically for INTEGER PRIMARY KEY)
NOT NULL a value is required
UNIQUE no two rows may share this value
CHECK (...) the value must pass this test
REFERENCES table(column) must match a row in another table (a foreign key)
DEFAULT value used when no value is given

With a CHECK (rating BETWEEN 1 AND 5), inserting a rating of 9 fails with an error. That is the point: the mistake is caught at once, not discovered months later.

Designing well

  • One table per kind of thing (customers, products, reviews).
  • Link tables with ids instead of copying names around.
  • Make columns NOT NULL unless "unknown" is a real possibility.

Your task

  1. Create a table reviews with columns:
    • id: integer primary key,
    • product_id: integer, required, referencing products(id),
    • rating: integer, required, from 1 to 5 (enforce it with CHECK),
    • comment: text, optional.
  2. Add two reviews: product 1, rating 5, Fast and light; product 5, rating 4, Comfortable.
query.sql
-- 1. Create the reviews table


-- 2. Add two reviews

SQLite runs right here in your browser. The first run downloads about 0.7 MB; after that it is instant.

Discussion

Stuck? Ask here. Be kind, and share hints rather than full answers.

Loading the discussion…