SQLite - Alter a Table

• Join Statements
• Update Data

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:

And then check the table with the .schema command:

sqlite> .schema Artists
CREATE 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:

And check it with a .tables command:

sqlite> .tables
Albums1  Artists

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

AlbumName             Year                                              
--------------------  --------------------------------------------------
Surfing with the Ali  1987                                              
Flying in a Blue Dre  1989                                              
Black 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).

So we now have the following tables in our database:

sqlite> .tables
Albums   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:

Verify that the data was inserted into the new table:

sqlite> SELECT * FROM Albums;
AlbumId   AlbumName                        ReleaseDate  ArtistId  
--------  -------------------------------  -----------  ----------
1         Killers                          1981         7         
2         Powerslave                       1984         7         
3         Surfing with the Alien           1987         1         
4         Heavy as a Really Heavy Thing    1995         11        
5         Yummy Yummy                      1994         17        
6         Out of the Loop                  2007         6         
7         Suck on This                     1989         13        
8         Pork Soda                        1993         13        
9         Sailing the Seas of Cheese       1991         13        
10        Flying in a Blue Dream           1989         1         
11        Black Swans and Wormhole Wizard  2010         1         
12        Somewhere in Time                1986         7         
13        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.

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                          7         
2         Powerslave                       7         
3         Surfing with the Alien           1         
4         Heavy as a Really Heavy Thing    11        
5         Yummy Yummy                      17        
6         Out of the Loop                  6         
7         Suck on This                     13        
8         Pork Soda                        13        
9         Sailing the Seas of Cheese       13        
10        Flying in a Blue Dream           1         
11        Black Swans and Wormhole Wizard  1         
12        Somewhere in Time                7         
13        Big Red Car                      17        

So we now have the following tables in our database:

sqlite> .tables
Albums   Albums1  Albums2  Artists

• Join Statements
• Update Data