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 4 of 6

Every employee is active

Level 4: Write it yourself · Exercise 1 of 2

Add an is_active column to employees: an integer that cannot be NULL and is 1 for every existing employee.

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

I would like help with this exercise

Each run starts from a fresh copy of the data, then shows the rows the question is about.

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run to see the rows the question is about.

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