ALTER TABLE
Three months after launch, the shop decides it needs customers' phone numbers. The customers table already holds thousands of rows. You do not rebuild it from scratch; you change its shape in place with ALTER TABLE, and the existing rows come along with you.
The rule
ALTER changes a table's shape in place: add, drop, rename, retype.
Every form starts with ALTER TABLE <name> and then says what to change:
ALTER TABLE customers ADD COLUMN phone VARCHAR(15);
ALTER TABLE customers DROP COLUMN phone;
ALTER TABLE customers RENAME COLUMN city TO town;
ALTER TABLE customers RENAME TO clients;These four work in PostgreSQL, MySQL 8 and recent SQLite. Changing a column's type is where dialects differ most:
ALTER TABLE products ALTER COLUMN name TYPE VARCHAR(200); -- PostgreSQL
ALTER TABLE products MODIFY COLUMN name VARCHAR(200); -- MySQLNotice that ALTER never touches the values in the rows you keep, apart from filling a new column; changing the data itself is UPDATE's job, which comes later in the course. You can also add or remove constraints, for example ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);. The database checks existing rows first, and refuses if any of them already break the new rule.
The shortcut
Before every ALTER, ask one question: what happens to the rows that are already there?
- Add a nullable column → existing rows get NULL. Safe.
- Add a NOT NULL column → existing rows need a value, so give a DEFAULT, or the statement fails.
- Drop a column → its data is gone for every row.
- Narrow a type (say
VARCHAR(200)toVARCHAR(20)) → rows that do not fit make it fail.
Try the obvious
ALTER TABLE customers ADD COLUMN phone VARCHAR(15) NOT NULL;— existing customers have no phone, so PostgreSQL refuses (and other databases may quietly fill in an empty string, which is worse).
On a very large, busy table, some ALTERs rewrite or lock the whole table. In production, find out how your database handles the change before running it.
Drills
Answer fast — trust the rule.
Which statement adds an email_verified column to customers?
You add loyalty_points INTEGER (no default, nullable) to a table with 500 rows. What do those rows hold in the new column?
ALTER TABLE customers DROP COLUMN email; — what happens to the emails already stored?
Why might ALTER TABLE products ADD COLUMN sku VARCHAR(20) NOT NULL; fail on a table that already has rows?
Remember
- ALTER changes a table's shape in place: add, drop, rename, retype.
- Always ask what happens to the rows already there.
- Adding NOT NULL to a populated table needs a DEFAULT; dropping a column destroys its data.