ALTER TABLE: changing a table's shape
Every lesson so far has queried tables whose shape was already fixed by
setup. In practice, a table's shape changes after it's created and full of
data - a new feature needs a new column. ALTER TABLE changes an existing
table's structure without touching the rows already in it.
ALTER TABLE users ADD COLUMN email TEXT NOT NULL DEFAULT '';
What just happened: every existing row instantly gets an email column set
to '' (the default), and the NOT NULL means no future row can leave it
out. ALTER TABLE itself doesn't return any rows to look at, though - so
this lesson's task is really two statements: the ALTER TABLE that makes the
change, and a SELECT afterward that proves it happened. Only that last
statement is what gets checked, same as the transactions lesson - write both,
in order, in the same script.
PRAGMA table_info(<table>) is a query built into SQLite that lists a
table's columns: name, declared type, whether it's NOT NULL, and its
default value - a live look at the schema, right from SQL.
There's a users table: id, name, age.
Your task: add a NOT NULL column called email to users with a
default value of '' (empty string), then run PRAGMA table_info(users) so
there's something to check.
You'll practice:
- Adding a column with
ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT ...
- Inspecting a table's schema with
PRAGMA table_info
- Writing a multi-statement script where only the final query is graded
Related reading: Doing It Safely on Live Data →