Set initial value for AUTOINCREMENT in SQLite

Question

How to set initial value for AUTOINCREMENT field in SQLite?

Answer 1

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

Edit: the following comment down, which discusses editing the SQLITE_SEQUENCE table, is probably more preferable: https://stackoverflow.com/a/316616/

Answer 2

On the SQLite website:

SQLite tracks the largest ROWID that a table has SQLITE_SEQUENCE using a special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created and initialized automatically when a normal table containing the AUTOINCREMENT column is created. The contents of the SQLITE_SEQUENCE table can be modified using the usual UPDATE, INSERT, and DELETE statements. But making changes to this table is likely to violate the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before making 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 the table is the name of the table.

Answer 3

I use the following query, which solves the problem when sqlite_sequence has no record for the table (i.e. the first record has not yet been added to the table), otherwise it updates the sequence.

 BEGIN TRANSACTION; UPDATE sqlite_sequence SET seq = <n> WHERE name = '<table>'; INSERT INTO sqlite_sequence (name,seq) SELECT '<table>', <n> WHERE NOT EXISTS (SELECT changes() AS change FROM sqlite_sequence WHERE change <> 0); COMMIT; 

Answer 4

One way to do this is to insert the first line , explicitly specifying the identifier of the line you want to start with . SQLite then inserts row identifiers that are higher than the previous one.

Answer 5

In the solution with the SQLITE_SEQUENCE table, the record in this table seems to be added after adding the first insert to the table with the addition of an auto-increment column. In some cases, this can cause problems (for example, auto-increment still starts at 1, and not from the desired value).

Answer 6

Now I used this as my solution:

 REPLACE INTO sqlite_sequence (name, seq) VALUES ('<table>', <n>) 

This work is for me. Thanks for the answer above.

Answer 7

Just wanted to add a few notes to a very grateful answer from iTech:

• The column name in sqlite_sequence is case sensitive. (Maybe it's just me, but based on other databases, I always assume that when comparing strings, case insensitive).
• SQLite seems reliable: if the number in sqlite_sequence is incorrect and will duplicate the rowid value, sqlite will use the next available number for rowid (verified with sqlite 3.28)
• The same is true if the row in sqlite_sequence is deleted.
• I used, as suggested in the comment, "WHERE IT DOESN'T EXIST SELECT a name from sqlite_sequence WHERE name = 'table'" instead of checking "changes ()"