With AUTOINCREMENT, rows with automatically selected ROWIDsĪre guaranteed to have ROWIDs that have never been used before by the same table in The behavior implemented by the AUTOINCREMENT keyword is subtly different from The SQLite documentation does explain that using the AUTOINCREMENT keyword means slower INSERTs:

For example I found out about this while looking into a TracPastePlugin bug, th:#4265, which exhibits the same behavior - the ID of a deleted paste will be reused by a new paste, if and only if the deleted paste is the most recently created, and the site is using the sqlite backend. This does not only affect tickets, and does not only affect Trac core - any plugin that defines a database table with a Column(., auto_increment=True) is also affected.

I believe this behavior only occurs with the SQLite backend. If you had instead deleted ticket #1 in step 6 above, the newly created ticket in step 7 would be #3. The newly created ticket will be #2, instead of #3. Start up an environment using the SQLite backend.Specifically, the behavior only differs in cases when a deleted row happens to have the largest existing value for its primary key. In order to prevent reuse of deleted values, the column must instead be created with type 'integer PRIMARY KEY AUTOINCREMENT.' auto_increment : ctype = 'integer PRIMARY KEY'Īccording to the SQLite documentation, this will assign a unique value to each existing row - but a newly created row might reuse a value that was previously used by a deleted row.

Sqlite autoincrement primary keys