Level 5 of 13 · Renovations

SQL ALTER TABLE: Add, Rename and Drop Columns

Changing a table after it exists: adding a column, renaming one, dropping one, renaming the table and changing a column type.

Covers: ADD COLUMN · RENAME COLUMN · DROP COLUMN · RENAME TO · MODIFY

Practise ALTER TABLE

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# Add a column: ALTER TABLE You need it when a table needs a new detail, like calories, or a column needs a new name or type, and the rows already in it must stay. ALTER TABLE changes a table's structure and keeps its rows. - `ALTER TABLE pizzas` change the pizzas table - `ADD COLUMN calories INT` add a new column at the end Rows that are already there get NULL in the new column. > Adding a column to a very large table can take a long time. On a live database, do it at a quiet time.

ALTER TABLE pizzas
ADD COLUMN calories INT;
SELECT * FROM pizzas

# A new column with a default - `DEFAULT 1` every existing row gets 1, and so does every new row that leaves it out > If the column must never be empty, add it with NOT NULL DEFAULT …, so old and new rows are both covered.

ALTER TABLE pizzas
ADD COLUMN in_stock INT DEFAULT 1;
SELECT name, in_stock FROM pizzas

# Rename a column - `RENAME COLUMN nickname TO alias` same data, new name > Renaming breaks every query, view and Appian record type that uses the old name. Search for it before you rename.

ALTER TABLE customers
RENAME COLUMN nickname TO alias;
DESCRIBE customers

# Rename a table - `RENAME TO clients` the table gets a new name, rows and all - `SHOW TABLES` lists every table in the database > Anything that still uses the old name, like a saved query or a view, now points at nothing.

ALTER TABLE customers
RENAME TO clients;
SHOW TABLES

# Remove a column: DROP COLUMN - `DROP COLUMN email` deletes the column and all of its data There is no undo, and no "are you sure?". > Take a backup, or copy the column somewhere safe, before you drop it.

ALTER TABLE customers
DROP COLUMN email;
SELECT * FROM customers

# Change a column's type: MODIFY COLUMN - `MODIFY COLUMN city VARCHAR(100) NOT NULL` city becomes longer text, and required The practice database in your browser can't run this one. > MODIFY replaces the whole column definition. Leave out NOT NULL or DEFAULT and they're gone, so copy the old definition first.

ALTER TABLE customers
MODIFY COLUMN city VARCHAR(100) NOT
  NULL;

Appian corner

When you change a table behind a record type, update the record type to match: add, rename or remove the field, then let the record type sync the new shape. Change a column a field depends on without telling the record type, and the next sync fails.

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

1. Add a calories column (whole number) to pizzas.
```
ALTER TABLE pizzas
___ ___ calories INT
```
Show the answer
```
ALTER TABLE pizzas
ADD COLUMN calories INT
```
Two words: what you are doing, and to what.
2. pizzas has 8 rows. After this, what is calories for the Margherita?
```
ALTER TABLE pizzas
ADD COLUMN calories INT
```
- 0
- NULL
- ''
- An error
Show the answer
NULL. Existing rows get NULL in a new column, unless you give it a DEFAULT.
3. Add a column in_stock to pizzas: a whole number, with DEFAULT 1 so every pizza starts in stock.
Show the answer
```
ALTER TABLE pizzas
ADD COLUMN in_stock INT DEFAULT 1
```
ADD COLUMN in_stock INT DEFAULT 1
4. Rename the nickname column of customers to alias.
Show the answer
```
ALTER TABLE customers
RENAME COLUMN nickname TO alias
```
RENAME COLUMN old TO new
5. Remove the email column from customers.
Show the answer
```
ALTER TABLE customers
DROP COLUMN email
```
DROP COLUMN email
6. City names are getting long. Change customers.city to VARCHAR(100), keeping the column and its data.
```
ALTER TABLE customers
___ COLUMN city VARCHAR(100)
```
Show the answer
```
ALTER TABLE customers
MODIFY COLUMN city VARCHAR(100)
```
The ALTER TABLE word that changes a column’s type.
7. Boss question! Rename the table orders to deliveries, then add a column driver to it (text up to 50).
Show the answer
```
ALTER TABLE orders
RENAME TO deliveries;
ALTER TABLE deliveries
ADD COLUMN driver VARCHAR(50)
```
Two ALTER TABLE statements: RENAME TO deliveries; then ADD COLUMN driver VARCHAR(50) on the new name.

Practise ALTER TABLE