Thomas Briggs wrote:
See http://www.sqlite.org/autoinc.html - INTEGER PRIMARY KEY will
autoincrement only until you delete a row from the table.
The above seems overstated.
INTEGER PRIMARY KEYs with or without the AUTOINCREMENT keyword behave
identically unless 1) the last row is deleted or 2) a row with the
highest possible ROWID exists or has existed in the table, and an
insertion is made.
In case 1) the ROWID used for the new row will be one more than the
current largest ROWID if the AUTOINCREMENT keyword is not present, and
one more than the largest value ever used in the table if the keyword is
present. In my mind both qualify as autoincrementing, with either being
fine for most purposes, but the latter behavior can certainly be needed
in some circumstances.
In case 2) an error is thrown if AUTOINCREMENT is present; if not, a
random ROWID is used if the row with the largest possible value is still
in the table, otherwise one more than the largest current value.
AUTOINCREMENT guarantees unique ROWID values for the life of the table;
without it values can be reused, but will always be unique at any given
time, and will autoincrement under normal circumstances.
Gerry
--
------------------
Gerry Snyder
American Iris Society Director, Symposium Chair
in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
