On 2016/11/01 11:52 AM, Simon Slavin wrote:
Unfortunately the response is going to be along the lines of "We can't do this for
compatibility reasons because there might be a program out there that does it.".
And rightly so...
There is no reason to use Autoincrement other to be sure newly added
items (in consecutive inserts) get an automatic next number as an
integer primary key that was never used before in an insert. I have been
vocal on here about how that should not really be left up to the DB
engine, you should really assign your own numbering, but, for easiness
sake, using the DB is fine.
Then, someone starts expecting miracles from the mechanism, which it may
provide by virtue of doing some fun things at a cost, and then someone
starts wanting it to "Lock down" the field because now the fun things
shouldn't always happen because....
I for one have many systems which update the Autoinc key, such as
inserting a new value in between two others and the like. It would break
bad if it locks up. I also do not wish the autoinc value to update
during updates, because I may alter all of them in a single transaction
and then back again after some shuffling and the like, at the end of
which the Autoinc next value should still be what it was before the
updates - not to mention saving valuable CPU cycles. On the occasion
that I DO need it to update, amending the sequence table that keeps the
next autoinc value is a simple thing to do.
Moral of the story: Don't use Autoincrement, and if you /have/ to -
control it well and test it well, and rest assured that updates won't
affect the next key value - unless you want to, in which case you can
simply pop the MAX(...)+1 into the autoinc keys control table
(sqlite_sequence).
An alternative would be to have UPDATE actually write the sequence
table, and then issue another update to it if you /didn't/ want the next
key to change - but that is:
A - Contrary to how it currently works (backwards compatibility etc.),
B - Not a huge audience for this method (14+ years of not a single
such a request),
C - Heavier on CPU cycles, and
D - Horrible if you up some key value significantly and then update
it back down, because there is no way the Autoinc value should *EVER* be
able/allowed to come back down. It's a one-way street.
cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
