sql · level 2 · unit 6 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
refuses a value that another row already has.
Two members may share a name, but never an email address. Add UNIQUE after a column's type and the database checks every new row against all the others.
Add an email that is already there and the statement stops with UNIQUE constraint failed, naming the table and column. The rows already in the table stay as they were.
PRIMARY KEY is unique too, which is why ids never repeat. NULL is different: a UNIQUE column can hold many empty boxes, because nothing equals NULL.
CREATE TABLE members (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
);
INSERT INTO members (name, email) VALUES ('Ava', 'ava@example.com'), ('Leo', 'leo@example.com');
SELECT * FROM members;Put UNIQUE on anything people look rows up by, like an email address or a username.
guess first
Two members called Sam. What happens?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE);
INSERT INTO members (name, email) VALUES ('Sam', 'sam@example.com');
INSERT INTO members (name, email) VALUES ('Sam', 'sam.b@example.com');
SELECT * FROM members;your turn
Your turn. Claim two usernames.
Type a different username in each blank, then run it. Afterwards, try the same username twice and read the error.
result
Your answer appears here as a table.
fix it
Leo cannot sign up. Fix it!
result
Your answer appears here as a table.
- Read the error. Which column does it name?
- Leo's row reuses Ava's email, and
emailisUNIQUE. - Change Leo's email to 'leo@example.com', then press run.
nailed it
no two rows can share an email now. :D
UNIQUEstops two rows sharing a value.- A repeat fails with
UNIQUE constraint failed. - A
UNIQUEcolumn can still hold manyNULLs.
new words in your collection
- UNIQUE
This lesson isn’t saved. A free account remembers every lesson you finish.