Practise · Change the shape, not the facts

Opening your saved work…

The reading

Change the shape, not the facts

ALTER TABLE employees
ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1;

Twenty-five existing employees, each now active. Open in the playground

ALTER TABLE shops ADD COLUMN manager_name TEXT adds a column to every existing row at once. Each row gets NULL in it, or the DEFAULT if you give one: ALTER TABLE employees ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1 gives every employee a 1. A NOT NULL column with no DEFAULT is refused, because the existing rows would have nothing to hold, and so is a UNIQUE column: the engine cannot invent distinct values for rows that already exist.

DROP TABLE menu_items_old removes the table and every row in it, in one statement, with no undo. IF EXISTS makes a rerun harmless, which is why build scripts start with DROP TABLE IF EXISTS only when the author means 'start over'. With foreign keys on, a parent table that other tables point at refuses to be dropped: DROP TABLE shops fails while employees and orders reference it. Before you type DROP, say which table must never be dropped and why.

The browser engine is an older SQLite. It can add a column and drop a table. It cannot rename a column or drop one; the desktop SQLite you use for the labs can do both. For anything an ALTER cannot do, such as changing a column's type or adding a constraint to an existing column, the pattern on both engines is the rebuild: create the new table with the right definition, INSERT ... SELECT the rows across, drop the old table, rename the new one.

Rule of thumb. ADD COLUMN touches every row; DROP TABLE takes the rows with it. Decide which table is untouchable before you type.

Going deeper (optional, not on the exam)

A DROP that is refused by a foreign key is the delete rule again, one level up: the parent cannot go while children point at it. A DROP that succeeds on a table nobody references is final the moment it runs.

Change the shape, not the facts · ALTER TABLE ADD COLUMN, DROP TABLE IF EXISTS, what the engine cannot do · level 1 of 6

A column for the manager's name

Level 1: Watch it answered · Exercise 1 of 1

The shops table needs a manager_name column. Watch the ALTER, run it, and look at what the existing rows hold.

Data: Northshore Coffee. Every run starts from a fresh copy, so nothing you try here is permanent.

Why it works, clause by clause

ALTER TABLE shops: the table whose shape changes.
ADD COLUMN manager_name TEXT: the new column and its type. No DEFAULT, so every existing row gets NULL in it.
Nothing else moves: the eight rows are still there with every other value intact.

From the reading: Change the shape, not the facts, paragraph 1

I would like help with this exercise

ALTER TABLE shops
ADD COLUMN manager_name TEXT;

Open in the playground

Query results

Run it to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.