This lesson's interactive features are locked, please to keep using them

Altering Tables

We often need to alter our database schema without deleting it and re-creating it. Imagine if Twitter deleted its database each time it needed to add a feature, that would be a disaster! Your account and all your tweets would be wiped out on a daily basis.

Instead, we can use the ALTER TABLE statement to make changes in place without deleting any data.

ALTER TABLE

With SQLite an ALTER TABLE statement allows you to:

1. Rename a Table or Column

ALTER TABLE employees
RENAME TO contractors;

ALTER TABLE contractors
RENAME COLUMN salary TO invoice;

2. Add or Drop a Column

ALTER TABLE contractors
ADD COLUMN job_title TEXT;

ALTER TABLE contractors
DROP COLUMN is_manager;

Unlike some SQL databases, SQLite does not support performing multiple operations (like adding multiple columns) in a single ALTER TABLE statement. Each change must be made in a separate ALTER TABLE command.

Assignment

We need to make some changes to the people table! At the moment, we have these six columns (shown as rows, so we can display datatypes):

CID	NAME	TYPE
0	id	INTEGER
1	tag	TEXT
2	name	TEXT
3	age	INTEGER
4	balance	REAL
5	is_admin	BOOLEAN

Rename the table to users.
In users, rename the tag column to username.
In users, add the password (TEXT) column.