Question

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

Can I make a field AUTOINCREMENT after made a table? For example, if you create a table like this:

create table person(id integer primary key, name text);

Then later on realise it needs to auto increment. How do I fix it, ie in MySQL you can do:

alter table person modify column id integer auto_increment

Is table creation the only opportunity to make a column AUTOINCREMENT?

 45  69858  45
1 Jan 1970

Solution

 55

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;
2012-05-05

Solution

 13

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/sqlite-users@sqlite.org/msg26987.html

2010-04-26