Creating and changing tables
🏠The everyday picture
Printing a new register with its column headings, adding a column later when a new rule needs it, and, rarely, retiring an old register altogether.
📘What it really is
- Create a table (you did this in Modules 52 and 53):
CREATE TABLE courses (
id integer PRIMARY KEY,
title text NOT NULL UNIQUE,
fee_paise integer NOT NULL CHECK (fee_paise >= 0),
starts_on text
) STRICT;
- Change a table:
ALTER TABLE courses ADD COLUMN level text;ALTER TABLE courses RENAME COLUMN starts_on TO start_date;-
SQLite allows only a few kinds of
ALTER; PostgreSQL allows many more (changing types, adding constraints). -
Remove a table:
DROP TABLE courses;deletes the table and all its data at once. There's no undo except a backup (Module 58). -
In real projects, schema changes are made through migrations: numbered, version-controlled SQL files applied in order, so every copy of the database (yours, the test server, production) changes the same way (Module 58).
🖱️Try it
Create the courses table above. Add a level column. Then create a practice table scratch, insert one row, and drop
it. Notice how quickly the data is gone.
✅Quick check
What does DROP TABLE do to the data inside?
Show the answer
Deletes it permanently, along with the table.
Why are schema changes made through migration files in real projects?
Show the answer
So every copy of the database changes in the same, recorded, repeatable way.