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

The table that must not be dropped

Level 6: Transfer it · Exercise 1 of 1

A rebuild script for the coffee database starts with DROP TABLE IF EXISTS for the tables it will recreate. In two or three sentences, say which table you would never let that script drop, how the engine itself would react if foreign keys are on, and what would happen to the data if they were off.

Your answer, about 25 words or more

0 words

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

I would like help with this exercise

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