How to delete or add column in SQLITE?

Question

ALTER TABLE SQLite

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

You can:

1. create new table as the one you are trying to change,
2. copy all data,
3. drop old table,
4. rename the new one.

I've wrote a Java implementation based on the Sqlite's recommended way to do this:

private void dropColumn(SQLiteDatabase db,
        ConnectionSource connectionSource,
        String createTableCmd,
        String tableName,
        String[] colsToRemove) throws java.sql.SQLException {

    List<String> updatedTableColumns = getTableColumns(tableName);
    // Remove the columns we don't want anymore from the table's list of columns
    updatedTableColumns.removeAll(Arrays.asList(colsToRemove));

    String columnsSeperated = TextUtils.join(",", updatedTableColumns);

    db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");

    // Creating the table on its new format (no redundant columns)
    db.execSQL(createTableCmd);

    // Populating the table with the data
    db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
            + columnsSeperated + " FROM " + tableName + "_old;");
    db.execSQL("DROP TABLE " + tableName + "_old;");
}

To get the table's column, I used the "PRAGMA table_info":

public List<String> getTableColumns(String tableName) {
    ArrayList<String> columns = new ArrayList<String>();
    String cmd = "pragma table_info(" + tableName + ");";
    Cursor cur = getDB().rawQuery(cmd, null);

    while (cur.moveToNext()) {
        columns.add(cur.getString(cur.getColumnIndex("name")));
    }
    cur.close();

    return columns;
}

I actually wrote about it on my blog, you can see more explanations there:

http://udinic.wordpress.com/2012/05/09/sqlite-drop-column-support/

As others have pointed out

It is not possible to rename a column, remove a column, or add or remove constraints from a table.

source : http://www.sqlite.org/lang_altertable.html

While you can always create a new table and then drop the older one. I will try to explain this workaround with an example.

sqlite> .schema
CREATE TABLE person(
 id INTEGER PRIMARY KEY, 
 first_name TEXT,
 last_name TEXT, 
 age INTEGER, 
 height INTEGER
);
sqlite> select * from person ; 
id          first_name  last_name   age         height    
----------  ----------  ----------  ----------  ----------
0           john        doe         20          170       
1           foo         bar         25          171       

Now you want to remove the column height from this table.

Create another table called new_person

sqlite> CREATE TABLE new_person(
   ...>  id INTEGER PRIMARY KEY, 
   ...>  first_name TEXT, 
   ...>  last_name TEXT, 
   ...>  age INTEGER 
   ...> ) ; 
sqlite> 

Now copy the data from the old table

sqlite> INSERT INTO new_person
   ...> SELECT id, first_name, last_name, age FROM person ;
sqlite> select * from new_person ;
id          first_name  last_name   age       
----------  ----------  ----------  ----------
0           john        doe         20        
1           foo         bar         25        
sqlite>

Now Drop the person table and rename new_person to person

sqlite> DROP TABLE IF EXISTS person ; 
sqlite> ALTER TABLE new_person RENAME TO person ;
sqlite>

So now if you do a .schema, you will see

sqlite>.schema
CREATE TABLE "person"(
 id INTEGER PRIMARY KEY, 
 first_name TEXT, 
 last_name TEXT, 
 age INTEGER 
);

DB Browser for SQLite allows you to add or drop columns.

In the main view, tab Database Structure, click on the table name. A button Modify Table gets enabled, which opens a new window where you can select the column/field and remove it.

SQLite 3.35.0 introduced support for ALTER TABLE DROP COLUMN.

ALTER TABLE

The DROP COLUMN syntax is used to remove an existing column from a table. The DROP COLUMN command removes the named column from the table, and also rewrites the entire table to purge the data associated with that column. The DROP COLUMN command only works if the column is not referenced by any other parts of the schema and is not a PRIMARY KEY and does not have a UNIQUE constraint.

The following syntax will be valid:

ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>;
ALTER TABLE <TABLENAME> DROP <COLUMNNAME>;

http://www.sqlite.org/lang_altertable.html

As you can see in the diagram, only ADD COLUMN is supported. There is a (kinda heavy) workaround, though: http://www.sqlite.org/faq.html#q11