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 NULLunless "unknown" is a real possibility.
Your task
- Create a table
reviewswith columns:id: integer primary key,product_id: integer, required, referencingproducts(id),rating: integer, required, from 1 to 5 (enforce it withCHECK),comment: text, optional.
- Add two reviews: product 1, rating 5,
Fast and light; product 5, rating 4,Comfortable.