Set start value for AUTOINCREMENT in SQLite

From the SQLite web site:

SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the SQLITE_SEQUENCE table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.

I tried this, and it works:

UPDATE SQLITE_SEQUENCE SET seq = <n> WHERE name = '<table>'

Where n+1 is the next ROWID you want and table is the table name.

Explicitly insert the value-1 into the table, then delete the row.

Edit: the next comment down, which discusses editing the SQLITE_SEQUENCE table directly is probably preferable: https://stackoverflow.com/a/692871/10093

One way to do it is to insert the first row specifying explicitly the row id you want to start with. SQLite will then insert row ids that are higher than the previous highest.

Just wanted to add a few notes to the very much appreciated answer from iTech:

• The name column in sqlite_sequence is case sensitive. (Perhaps its only me, but coming from other databases I always assume that string comparison is case insensitive).
• SQLite seems to be robust: if the number in sqlite_sequence is wrong and would lead to a duplicated rowid value, sqlite will use the next available number for the rowid (checked with sqlite 3.28)
• Same is true if the row in sqlite_sequence gets deleted.
• I used as suggested in a comment the "WHERE NOT EXISTS SELECT name from sqlite_sequence WHERE name = 'table'" instead of checking "changes()"

I tried this and it works good: FOR INSERT

INSERT INTO sqlite_sequence (name, seq) VALUES ('<table name>', <value>)

TO UPDATE

UPDATE sqlite_sequence SET  seq = <value> WHERE name= '<table name>'