SQLite database ..is the value of primary key effected if i delete a row from table

Question

suppose i build a table with 10 rows initially. so the value of the id(primary key column) at the end will be 9 or 10 ? and now suppose i delete row no. 5 and 8 (assuming rows counting start from 1) and add one another row so that now i have 9 rows in total. now what will happen to the values of id in each row.?

      db.delete(DBAdapter.TableName, "Id=? AND QstnrId=? AND QstnId=?",
      new String[] { Id.toString(), QuestionnaireId, QuestionId });

Answer 1

An autoincrement primary key column in SQLite uses a value 1 larger than the largest value that has ever been used in the table:

From the documentation:

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table. If the table has never before contained any data, then a ROWID of 1 is used. If the table has previously held a row with the largest possible ROWID, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error.

So, deleting a row has no effect on the next ID generated.

Note: In the case of non-primary keys, it only uses the value of the current largest ID.

Also, deleting a row will not change the values of another row, so if you have ID's 1, 2, 3, 4 and delete the row with ID 1, the remaining rows will still have ID's 2, 3, 4. Auto-increment is just a method of making inserts easier.

Answer 2

the values of id for the earlier rows will remain same...

For new ones, it will start form 10/11...

so the list of ids would be : 1 2 3 4 6 7 9 10 11 12 ,.... as in every other SQL Database