SQLite supports a limited subset of the SQL standard ALTER TABLE statement.

In SQLite, the ALTER TABLE command allows the user to rename a table or to add a new column to an existing table.

Add a Column

The ADD COLUMN syntax is used to add a new column to an existing table.

Let's add a column to the Artists table:

ALTER TABLE Artists

ADD COLUMN Bio TEXT;

And then check the table with the .schema command:

sqlite> .schema ArtistsCREATE TABLE Artists(ArtistId    INTEGER PRIMARY KEY,  ArtistName  TEXT NOT NULL, Bio TEXT);

The following restrictions apply when using the ADD COLUMN syntax in SQLite:

• The column may not have a PRIMARY KEY or UNIQUE constraint.
• The column may not have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses.
• If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
• If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.

Rename a Table

The RENAME TO syntax allows you to change the name of a table.

Let's rename one of our tables:

ALTER TABLE Albums

RENAME TO Albums1;

And check it with a .tables command:

sqlite> .tablesAlbums1  Artists

Now, any further operations will need to use the new name of the table. So we can select data like this:

SELECT AlbumName, Year 

FROM Artists AS a INNER JOIN Albums1 AS r

ON a.ArtistId = r.ArtistId

WHERE a.ArtistName = 'Joe Satriani';

AlbumName             Year--------------------  --------------------------------------------------Surfing with the Ali  1987Flying in a Blue Dre  1989Black Swans and Worm  2010

Updating or Dropping Columns

SQLite does not support clauses such as DROP COLUMN, ALTER COLUMN, and ADD CONSTRAINT, which are part of the SQL standard.

However, there are other ways of doing these things with SQLite.

Alter a Column

Say we want to change the name of the Year column to ReleaseDate. Also, we want to remove the NOT NULL restriction.

To do this, you can create a new table (with the new column definition), populate the table with the data from the old table, then once complete, delete the old table and rename the new one to reflect the original name.

Create the New Table

First, create the new table (notice ReleaseDate instead of Year).

CREATE TABLE Albums(

AlbumId     INTEGER PRIMARY KEY, 

AlbumName   TEXT NOT NULL,

ReleaseDate TEXT,

ArtistId INTEGER NOT NULL,

FOREIGN KEY(ArtistId) REFERENCES Artists(ArtistId)

);

So we now have the following tables in our database:

sqlite> .tablesAlbums   Albums1  Artists

Insert Data

Then insert the data from the old table.

Use an INSERT statement that selects data from the old table and inserts it into the new table. Like this:

INSERT INTO Albums (AlbumId, AlbumName, ReleaseDate, ArtistId)

SELECT AlbumId, AlbumName, Year, ArtistId

FROM Albums1;

Verify that the data was inserted into the new table:

sqlite> SELECT * FROM Albums;AlbumId   AlbumName                        ReleaseDate  ArtistId--------  -------------------------------  -----------  ----------1         Killers                          1981         72         Powerslave                       1984         73         Surfing with the Alien           1987         14         Heavy as a Really Heavy Thing    1995         115         Yummy Yummy                      1994         176         Out of the Loop                  2007         67         Suck on This                     1989         138         Pork Soda                        1993         139         Sailing the Seas of Cheese       1991         1310        Flying in a Blue Dream           1989         111        Black Swans and Wormhole Wizard  2010         112        Somewhere in Time                1986         713        Big Red Car                      1995         17

Drop the Old Table

Now that the new table has been created and populated with the data, we have the choice of removing the old table, modifying it, or leaving it as is.

Let's leave it for now, we'll remove it later.

Drop a Column

To drop a column, you can create a table from a SELECT statement. In the SELECT statement, omit the column/s that you want dropped — SQLite will create only those columns that are included in the SELECT statement.

CREATE TABLE Albums2 AS 

SELECT AlbumId, AlbumName, ArtistId 

FROM Albums1;

And then check to see that the data has successfully been inserted into the new table:

sqlite> select * from Albums2;AlbumId   AlbumName                        ArtistId--------  -------------------------------  ----------1         Killers                          72         Powerslave                       73         Surfing with the Alien           14         Heavy as a Really Heavy Thing    115         Yummy Yummy                      176         Out of the Loop                  67         Suck on This                     138         Pork Soda                        139         Sailing the Seas of Cheese       1310        Flying in a Blue Dream           111        Black Swans and Wormhole Wizard  112        Somewhere in Time                713        Big Red Car                      17

So we now have the following tables in our database:

sqlite> .tablesAlbums   Albums1  Albums2  Artists