On 2015/12/09 2:21 PM, David Baird wrote: > On Wed, Dec 9, 2015 at 7:14 AM, Clemens Ladisch <clemens at ladisch.de> wrote: > > Just tried two different versions: > > SQLite version 3.8.2 2013-12-06 14:53:30 (that is bundled with my OS): has > the undesired behavior > > SQLite version 3.9.2 2015-11-02 18:31:45 (that I built myself): has desired > behavior > > Looks like it's fixed, as long as I stick to new versions. *cross fingers* > Thanks!
There is no reason on Earth to trust the auto-incrementing to produce an exact sequence, and no reason to need it. You may well assign the PK yourself and not depend on autoincrement - which is what I would very much advise if you are depending on the specific values or order of assigned values ending up in that column. Autoincrement is just a side-facility to allow lazy programming (not meant in a bad way, I use it too), but it only promises uniqueness, not order - and you should not expect/depend on anything that is not explicitly promised. You can at any time insert a value with a negative ID, for instance, and the next auto ID will likely not be the one directly following that. Also - your version of the table is not necessarily reflecting the actual rowid alias though it might work like that (but I don't think it is documented like that - it is only promised when you have the specific phrase: "Col INTEGER PRIMARY KEY" with the optional autoincrement specifier). Cheers, Ryan