Changing the schema

A table's design is not fixed. ALTER TABLE … ADD adds a new column to an existing table:

ALTER TABLE product ADD weight DECIMAL(5, 2);

Existing rows get NULL in the new column until you fill them in.

DROP TABLE deletes a whole table — its structure and all its rows:

DROP TABLE product;

This cannot be undone, so use it with care. Dropping a table that other tables reference can break referential integrity.

Question. Write the SQL to add a field email, which holds up to 40 characters, to the table customer.

Answer.

ALTER TABLE customer ADD email VARCHAR(40);

Reading the table afterwards shows the new field at the end, empty for every customer who already existed:

id | name | city   | email
1  | Ada  | London | NULL
2  | Ben  | Leeds  | NULL
3  | Cara | London | NULL
4  | Dan  | Hull   | NULL

NULL means "no value yet". An UPDATE (lesson 15) fills it in.

A-Level papers also use ALTER TABLE to add a key to a table that already exists:

ALTER TABLE Orders ADD PRIMARY KEY (OrderID);
ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID);

SQLite cannot add a key to an existing table, so these two do not run here. In this playground, declare keys in CREATE TABLE (lesson 12).

• DROP TABLE and DELETE FROM are different. DELETE FROM product empties the rows but keeps the table; DROP TABLE product removes the table itself.
• ADD needs a data type. ALTER TABLE customer ADD email gives the column no type; write ADD email VARCHAR(40).
• Existing rows get NULL in a new column unless you set a default.