1

I have a table in my database with one field declared as 

_id integer primary key

There is no AUTOINCREMENT.

Here i have a doubt about how rowid will be updated during insert after a delete. what i observed is

  1. if the last row id is x and it is deleted, the next row inserted gets id x.

But if i delete some other rows with id < x and then insert a new row the rowid of the newly inserted row is x+1 .

Please confirm is my observation right ( rowid reused only if the last rows are deleted and not in between rows. )

I saw the explanation for AUTOINCREMENT. I want to know the behaviour when no AUTOINCREMENT is set

png
  • 4,368
  • 7
  • 69
  • 118

2 Answers2

3

Without an autoincrement set, the row would be an alias for the ROWID...

This is what the FAQ says:

If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used. If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with an SQLITE_FULL error. If no negative ROWID values are inserted explicitly, then automatically generated ROWID values will always be greater than zero.

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

Anthony Graham
  • 451
  • 4
  • 13
  • Thank you. This is the behaviour when auto-increment is set. I want to know if by default an integer primary key is autoincrement. I do not specify and row value during insert. – png Mar 12 '12 at 07:42
  • No. This is the behavior when auto increment is NOT set. – Anthony Graham Mar 12 '12 at 07:49
  • ie with AUTO INCREMENT, the value will always be x+1 where x is the max id that has ever been in the table (not reusing values even if deleted) my post explains how values are reused by ROWID which should answer your question. (if you delete the max id from the table it will get reused, if the table uses the max ID it will search for a free LOWER ID, this will not happen with AUTO INCREMENT set) – Anthony Graham Mar 12 '12 at 08:02
3

The AUTOINCREMENT keyword is not required for SQLite tables to generate primary key values automatically.. what ever you add to the database will be added as the last row...so if u delete last row(x).. new element will be added at x position.. if you delete from middle or not.. new element on addition will go to x+1 position...

on using auto increment.. if you delete last element at x position next id x+1 will be generated by autoincrement algorithm.. makin the next addition at x+1 position..

5hssba
  • 8,079
  • 2
  • 33
  • 35
  • This is the behavior that i am seeing. Is there any document explaining this. Please share. – png Mar 12 '12 at 07:55
  • And what happend if you have 9223372036854775807 rows and delete some (not the last one?) see my answer. – Anthony Graham Mar 12 '12 at 08:05
  • on using autoincrement, or not the next addition will go to 9223372036854775807+1 row...[if not deleting the last row]..if you delete the last row.. 9223372036854775807+1 position on using autoincrement.. in this case 9223372036854775807 this entry is empty..if you dont use auto increment next addition will go to 9223372036854775807 position... this is what i understood from document.. if i am wrong plese correct me.. =] – 5hssba Mar 12 '12 at 08:39