How do I rename a column in a SQLite database tabl

Question

I would need to rename a few columns in some tables in a SQLite database. I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was not mentioned.

From the SQLite documentation for ALTER TABLE, I gather that it's not possible to do such a thing "easily" (i.e. a single ALTER TABLE statement).

I was wondering someone knew of a generic SQL way of doing such a thing with SQLite.

Answer 1

As mentioned before, there is a tool SQLite Database Browser, which does this. Lyckily, this tool keeps a log of all operations performed by the user or the application. Doing this once and looking at the application log, you will see the code involved. Copy the query and paste as required. Worked for me. Hope this helps

Answer 2

Say you have a table and need to rename "colb" to "col_b":

First you rename the old table:

ALTER TABLE orig_table_name RENAME TO tmp_table_name;

Then create the new table, based on the old table but with the updated column name:

CREATE TABLE orig_table_name (
  col_a INT
, col_b INT
);

Then copy the contents across from the original table.

INSERT INTO orig_table_name(col_a, col_b)
SELECT col_a, colb
FROM tmp_table_name;

Lastly, drop the old table.

DROP TABLE tmp_table_name;

Wrapping all this in a BEGIN TRANSACTION; and COMMIT; is also probably a good idea.

Answer 3

change table column < id > to < _id >

 String LastId = "id";

    database.execSQL("ALTER TABLE " + PhraseContract.TABLE_NAME + " RENAME TO " + PhraseContract.TABLE_NAME + "old");
    database.execSQL("CREATE TABLE " + PhraseContract.TABLE_NAME
    +"("
            + PhraseContract.COLUMN_ID + " INTEGER PRIMARY KEY,"
            + PhraseContract.COLUMN_PHRASE + " text ,"
            + PhraseContract.COLUMN_ORDER  + " text ,"
            + PhraseContract.COLUMN_FROM_A_LANG + " text"
    +")"
    );
    database.execSQL("INSERT INTO " +
            PhraseContract.TABLE_NAME + "("+ PhraseContract.COLUMN_ID +" , "+ PhraseContract.COLUMN_PHRASE + " , "+ PhraseContract.COLUMN_ORDER +" , "+ PhraseContract.COLUMN_FROM_A_LANG +")" +
            " SELECT " + LastId +" , "+ PhraseContract.COLUMN_PHRASE + " , "+ PhraseContract.COLUMN_ORDER +" , "+ PhraseContract.COLUMN_FROM_A_LANG +
            " FROM " + PhraseContract.TABLE_NAME + "old");
    database.execSQL("DROP TABLE " + PhraseContract.TABLE_NAME + "old");

Answer 4

Create a new column with the desired column name: COLNew.

ALTER TABLE {tableName} ADD COLUMN COLNew {type};

Copy contents of old column COLOld to new column COLNew.

INSERT INTO {tableName} (COLNew) SELECT {COLOld} FROM {tableName}

Note: brackets are necessary in above line.

Answer 5

One option, if you need it done in a pinch, and if your initial column was created with a default, is to create the new column you want, copy the contents over to it, and basically "abandon" the old column (it stays present, but you just don't use/update it, etc.)

ex:

alter table TABLE_NAME ADD COLUMN new_column_name TYPE NOT NULL DEFAULT '';
update TABLE_NAME set new_column_name = old_column_name;
update TABLE_NAME set old_column_name = ''; -- abandon old column, basically

This leaves behind a column (and if it was created with NOT NULL but without a default, then future inserts that ignore it might fail), but if it's just a throwaway table, the tradeoffs might be acceptable. Otherwise use one of the other answers mentioned here, or a different database that allows columns to be renamed.

Answer 6

First off, this is one of those things that slaps me in the face with surprise: renaming of a column requires creating an entirely new table and copying the data from the old table to the new table...

The GUI I've landed on to do SQLite operations is Base. It's got a nifty Log window that shows all the commands that have been executed. Doing a rename of a column via Base populates the log window with the necessary commands:

These can then be easily copied and pasted where you might need them. For me, that's into an ActiveAndroid migration file. A nice touch, as well, is that the copied data only includes the SQLite commands, not the timestamps, etc.

Hopefully, that saves some people time.