How to Add or Remove Columns from Sqlite Tables
Sqlite is a lightweight and powerful database engine. It is used mobile platforms such as Android and iOS as the default database engine. In many of my Android applications, I use pre-populated sqlite databases for data that changes rarely. As I released new versions of the app, sometimes I had to add, remove or rename columns of sqlite tables with data. Interestingly depending on the version of sqlite used in your machine, there are multiple ways to alter columns of sqlite tables.
If you are using sqlite 3.35 or later you can use the following commands,
- Add a column - ALTER TABLE [table name] ADD COLUMN [column name]
- Rename a column - ALTER TABLE [table name] RENAME COLUMN [column name]
- Remove a column - ALTER TABLE [table name] DROP COLUMN [column name]
However if you are using sqlite versions between 3.25 and 3.35, you have access only to the following commands,
- Add a column - ALTER TABLE [table name] ADD COLUMN [column name]
- Rename a column - ALTER TABLE [table name] RENAME COLUMN [column name]
If you are using sqlite version 3.2, you have access only to the following command,
- Add a column - ALTER TABLE [table name] ADD COLUMN [column name]
None of the above commands are available if you are using sqlite versions prior to 3.2. However you can use a multi-step process given below to migrate tables without loosing data,
- Rename the current table
- Create a new table with previous name
- Copy data from renamed table to the new table
- Drop the renamed table
Let me illustrate the various options by using a sample scenario. Assume we have a customer table which contains columns id, fullname and email.
CREATE TABLE customer( id VARCHAR(10) PRIMARY KEY, fullname VARCHAR(128), email VARCHAR(128));
Assume we want to drop the email column and then add an address column. In Sqlite 3.35 and above you can use the following commands,
ALTER TABLE customer ADD COLUMN address VARCHAR(128); ALTER TABLE customer DROP COLUMN address;
In older versions, you can use the following generic approach.
1. Rename the current customer table,
ALTER TABLE customer RENAME TO old_customer;
2. Create a new customer table,
CREATE TABLE customer( id VARCHAR(10) PRIMARY KEY, fullname VARCHAR(128), address VARCHAR(128));
3. Copy data from old table (note that address field will be blank for all rows),
INSERT INTO customer SELECT id, fullname,"" FROM old_customer ;
4. Finally delete the old table,
DROP TABLE old_customer;
Alternatively you can use a tool such as Sqlite Browser to change database structure. In Sqlite browser, click on database structure and then select the table. Click modify table button on top. From the next window you can add, remove or modify order of columns.