Changing Auto Increment of a colum in SQLite

Question

I am creating a table that at first keyID column is NOT in AUTO INCREMENT since i am going to insert a data that came from an old database where the keyID is already been set to each rows and it's not possible to be changed. So is it possible that i can set the column to AUTO INCREMENT after all the data has been moved? I've seen this:

ALTER TABLE tbl_name MODIFY COLUMN keyID_id INT auto_increment

So if ever i set the keyID to auto increment after i moved all the data, so for example 10 rows has been transferred and some numbers may be missing for example, keyIDs: 1,2,3,5,6,7...15

can I assume that after i set the keyID to auto increment, the next row will be keyID 16?

You *can* insert explicit values into an autoincrement column. — CL., Apr 12 '16 at 07:07
so are you saying... I can create the table column auto increment and i can insert data into it with specific ID? — Mon Tolentino, Apr 12 '16 at 07:09
The [documentation](http://www.sqlite.org/autoinc.html) says: "On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer …" — CL., Apr 12 '16 at 07:13

score 0 · Answer 1 · answered Apr 11 '16 at 20:35

The first thing to say here is that SQLite has no syntax to change the settings of a column. You are forced to rename your original table, create a new table with the schema you want and then copy from the renamed table into the new one.

So your code to add the AUTOINCREMENT flag to your table is something like this

SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = yourConnection;
cmd.CommandText = "ALTER TABLE tblName RENAME TO tmp_tblName";
cmd.ExecuteNonQuery();
cmd = new SQLiteCommand(@"
    CREATE TABLE tblName 
    (ID INTEGER PRIMARY KEY AUTOINCREMENT, 
     AnotherField NVARCHAR(100) NOT NULL)", cnn);
cmd.ExecuteNonQuery();             
cmd.CommandText = "INSERT INTO tblName (ID, AnotherField) SELECT ID, AnotherField FROM tmp_tblName";
cmd.ExecuteNonQuery();

Now, when you create a table with the AUTOINCREMENT SQLite add a new row to an internal table called sqlite_sequence. This table could be read by the usual ADO.NET classes

cmd.CommandText = "SELECT seq FROM sqlite_sequence WHERE name = 'tblName'";
int lastInsertedValue = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine(lastInsertedValue);

If your run this test code you will see that the value for the field seq is equal to the maximum value inserted in the PRIMARY KEY field. After this if you try to insert a new record in your table you will see that the AUTOINCREMENT flag has started to work as expected and your new record will receive the next value from the sequence....

score 0 · Answer 2 · edited May 23 '17 at 12:31

Even though the SQLite documentation and some posts state that the alter table command in SQLite is very limited and should not allow making such modifications, it seems that it's still possible to alter PK properties. What worked for me was:

Create a table with CREATE TABLE "test" (Field1INTEGER,Field2TEXT,)
Insert arbitrary data
Change column definition for column Field1 with DB Browser for SQLite Subsequent inserts should then auto increment the IDs in the column Field1.

I must admit that it might not be suitable if there's the requirement to do this programatically, but if some manual steps are acceptable, it may be a solution.

Changing Auto Increment of a colum in SQLite

2 Answers2