On 6/14/2017 5:42 AM, R Smith wrote:
On 2017/06/14 7:08 AM, Wout Mertens wrote:
Is there a way to specify the starting rowid when using autoincrement?
Or should I insert and then remove a row with the id set to one less than
the desired id?
This is quite easy, but first it is helpful to understand the mechanism
by which SQLite keeps track of the Primary Key Auto-Inc value.
If you define a primary key that is of type INT, and omit the
AUTOINCREMENT directive, then you will still have a primary key that
increments if you don't specify the value directly by virtue of primary
keys being UNIQUE and requires a value, so it's safe to bet if you
adding a key without specifying the value for it, you intend for it to
be automatic.
BUT, the next increment value depends on the DB engine guessing what it
should be based on existing key values (which can cause re-used keys
that used to exist for now-deleted items).
If you do define the AUTOINCREMENT directive, then SQLite promises to
always increment the value by one from the last time a value was
inserted - whether that value has been deleted or changed etc. - i.e. it
promises to never re-use a key. It achieves this by keeping a table,
namely the "sqlite_sequence" system-generated table, with references to
each table using AUTOINCREMENT and its Key based on the last value used
for the referred table.
You can simply change the values in this reference table to inform the
next AUTOINCREMENTed value you would like for the specific table-name.
I'm not sure now if references inside this sqlite_sequence table exists
the moment you create a table with an AUTOINCREMENT key, or only once
you insert for the first time, but it is easy to check and handle both
ways.
Taking all this into account, that is why (as another post suggested)
the equivalent in SQLite for other SQL DB's:
ALTER TABLE myTable AUTOINCREMENT = 5;
would be something like:
UPDATE sqlite_sequence SET seq = 5 WHERE name = 'myTable';
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
It is worth noting that there is no entry for a table with an
autoincrement column in sqlite_sequence until that table has at least
one row, so you need to perform an INSERT instead of an UPDATE (I don't
know if sqlite_sequence has a unique constraint on name, so I don't know
if REPLACE would work for both cases).
sqlite> create table t1 (c1 integer primary key autoincrement,c2 text);
sqlite> select * from sqlite_sequence;
sqlite> insert into t1 (c2) values ('stuff');
sqlite> select * from sqlite_sequence;
t1|1
sqlite>
Peter
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
