Changing and Deleting Tables

Tables change over time: the shop starts to collect a new piece of data, or a name turns out to be unclear. You don't have to delete the table with all its rows and create it again: ALTER TABLE changes a table that's already there. ADD COLUMN adds a column to it:

ALTER TABLE customers
ADD COLUMN phone TEXT;

SELECT name, phone
FROM customers
LIMIT 2;

The new column is added at the end of the table. The rows that are already there get NULL in it, or the column's DEFAULT. That's why a new column with NOT NULL needs a DEFAULT: without one, the rows that are already there would break the rule:

ALTER TABLE customers
ADD COLUMN newsletter INTEGER NOT NULL;

Error on line 1: Cannot add a NOT NULL column with default value NULL

With DEFAULT 0, it works, and every customer gets 0. RENAME COLUMN renames a column, and RENAME TO the whole table:

ALTER TABLE customers
RENAME COLUMN joined TO signed_up;

ALTER TABLE order_items
RENAME TO order_lines;

SQLite changes the name everywhere in the database, for example in the foreign keys of other tables. But the queries in an app's code still use the old name, and they'd stop working, so in a real app, tables and columns are renamed rarely and carefully. Besides this, SQLite's ALTER TABLE can only delete a column (DROP COLUMN). It can't change a column's type or add NOT NULL, UNIQUE or CHECK to a column that's already there. For that, you create a new table as it should be, copy the rows into it with INSERT … SELECT, delete the old table and give the new one its name. Other databases can do more.

DROP TABLE reviews; deletes a table with all its rows. There's no “Are you sure?” and no undo; only inside a transaction can ROLLBACK bring the table back. A table that other tables point at with their foreign keys can't be deleted while they have rows pointing at it, so the tables that point at it have to go first:

DROP TABLE games;

Error on line 1: FOREIGN KEY constraint failed

DROP TABLE of a table that doesn't exist is an error, and so is CREATE TABLE of a table that already exists. In scripts that may run more than once, programmers write DROP TABLE IF EXISTS and CREATE TABLE IF NOT EXISTS: when the table isn't there, or already is, the statement simply does nothing. The Tables tab keeps showing the tables as the exercise starts with them; your changes are seen only by the statements in your file.

Typical mistake: mixing up DROP TABLE and DELETE FROM. DELETE FROM reviews; deletes all the rows, but the empty table stays, ready for new ones. DROP TABLE reviews; deletes the table itself, with its columns and rules.