Can I alter a column in an sqlite table to AUTOINCREMENT after creation?

Question

You can dump the content to a new table:

CREATE TABLE failed_banks_id (id integer primary key autoincrement, name text, city text, state text, zip integer, acquired_by text, close_date date, updated_date date);

INSERT INTO failed_banks_id(name, city, state, zip, acquired_by,close_date, updated_date)
SELECT name, city, state, zip, acquired_by,close_date, updated_date
FROM failed_banks;

And rename the table:

DROP TABLE failed_banks;
ALTER TABLE failed_banks_id RENAME TO failed_banks;

Background:

The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table. To create keys that are unique over the lifetime of the table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration.

http://www.sqlite.org/faq.html#q1

SQLite limitations:

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.

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

Hack seems to exist:

It appears that you can set

PRAGMA writable_schema=ON;

Then do a manual UPDATE of the sqlite_master table to insert an "id INTEGER PRIMARY KEY" into the SQL for the table definition. I tried it and it seems to work. But it is dangerous. If you mess up, you corrupt the database file.

http://www.mail-archive.com/[email protected]/msg26987.html

From the SQLite Faq

Short answer: A column declared INTEGER PRIMARY KEY will autoincrement

So when you create the table, declare the column as INTEGER PRIMARY KEY and the column will autoincrement with each new insert.

Or you use the SQL statment ALTER to change the column type to an INTEGER PRIMARY KEY after the fact, but if your creating the tables yourself, it's best to do it in the initial creation statement.

Simplest way — Just export and re-import

It is possible, and relatively easy. Export the database as an sql file. Alter the SQL file and re-import:

  sqlite3 mydata.db .dump > /tmp/backup.sql
  vi /tmp/backup.sql
  mv mydata.db mydata.db.old
  sqlite3 mydata.db
  sqlite>.read /tmp/backup.sql