On 2015/12/09 10:35 PM, Wade, William wrote: > I may be reading different documentation (or perhaps just misreading it). > > https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, > which applies to the original post) "If no ROWID is specified on the insert > ... [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." > > And goes on to strongly imply that "not usual" means either the table is > currently empty, or the largest ROWID is already at the largest possible > integer. The implication is pretty strong that leaving ROWID unspecified on > an insert is equivalent (in the usual case) to specifying MAX(ROWID) + 1. > > I understand the difference between "documenting some details about how > things currently happen to behave" and "documenting the intended behavior." > When I read about the -journal or -wal files, I'm usually happy with the > thought that in the future SQLite may support (or use exclusively) an > entirely different rollback mechanism. It isn't entirely obvious which > category autoinc.html falls into, but I believe that a reasonable reader can > interpret that as documenting intended behavior.
You are correct, but I think misreading what Richard said. Richard specifically referred to what the OP stated is /his/ "desired" behaviour (hence the quotes), not what is the SQLite Dev's or SQLite or indeed the SQL standard's desired behaviour, and Richard mentioned that THAT specific behaviour is indeed NOT the way it works or is documented. To be clear, it is documented very specifically that in the absence of AUTOINCREMENT, any value that isn't yet used for the INT PK may be used, typically (or usually) the MAX(pk)+1. It then goes on to say that when AUTOINCREMENT IS indeed specified, you can be guaranteed that any value that was ever used before cannot be used again, regardless of whether the data was deleted and such. (i.e. specifically NOT MAX(pk)+1). Hope that makes more sense! Ryan
