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

Admin 1
Viewed 62725

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?

sqlite
11 Answers

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.

Bryan_Bailliache_747820

While the Sqlite site gives you an example how to do it with a table with only a three fields, it gets nasty with one of 30 fields. Given you have a table called OldTable with many fields, the first of which is "ID" filled with integers. Make a copy of your database for backup. Using the command program dot commands,

    .output Oldtable.txt
    .dump Oldtable
    Drop Table Oldtable;

Open Oldtable.txt in Microsoft Word or a grep like text editor. Find and Replace your Integer field elements with NULL.(You may need to adjust this to fit your fields). Edit the Create Table line so the field that was defined as Integer is now INTEGER PRIMARY KEY AUTOINCREMENT. Save as NewTable.txt

Back in the command program dot

   .read NewTable.txt

Done. ID is now autoincrement.

user_12121080

Yes Do you have phpmyadmin installed? I believe if you go to the 'structure' tab and look along the right columnn (where the field types are listed) - I think you can change a setting there to make it autoincrement. There is also a SQL query that will do the same thing.

user_650250

You cannot alter columns on a SQLite table after it has been created. You also cannot alter a table to add an integer primary key to it.

You have to add the integer primary key when you create the table.

Paul_Lefebvre_256150

you can alter the table, altering the column definition

user_728580
Related