sql · level 2 · unit 6 · lesson 3
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
refuses any row that fails your test.
A price of minus 5 makes no sense, and nor does 6 stars out of 5. CHECK lets you write the rule yourself, as a test in brackets after the column.
Every row that goes in, or gets changed by UPDATE, must pass. If one fails, the statement stops with CHECK constraint failed, followed by your test.
Tests can use anything a WHERE can, like CHECK (stars >= 1 AND stars <= 5) or CHECK (size IN ('S', 'M', 'L')).
CREATE TABLE cakes (
name TEXT NOT NULL,
price REAL CHECK (price > 0)
);
INSERT INTO cakes VALUES ('Brownie', 1.5), ('Scone', 1.25);
SELECT * FROM cakes;A test on a NULL counts as a pass, so pair CHECK with NOT NULL when the box must be filled.
guess first
A review with 6 stars. What happens?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
CREATE TABLE reviews (film TEXT, stars INTEGER CHECK (stars >= 1 AND stars <= 5));
INSERT INTO reviews VALUES ('Ocean Cats', 4);
INSERT INTO reviews VALUES ('Robot Picnic', 6);
SELECT * FROM reviews;your turn
Your turn. Only sensible sizes.
Type three sizes in the first blank, each in single quotes with commas between, like 'S', 'M', 'L'. Then order a blue shirt in one of them.
result
Your answer appears here as a table.
fix it
The rule refuses a fair price. Fix it!
result
Your answer appears here as a table.
- Read the error. Is 80 a sensible price?
- The test says a price must be below 0, which is the wrong way round.
- Change price < 0 to price > 0, then press run.
nailed it
bad values cannot get in any more. :D
CHECK (test)refuses rows that fail the test.- It guards
UPDATEas well asINSERT. - Tests can use
AND,ORandIN, likeWHERE.
new words in your collection
- CHECK
This lesson isn’t saved. A free account remembers every lesson you finish.