65

How can I set the start value for an AUTOINCREMENT field in SQLite?

Christian Davén
  • 16,713
  • 12
  • 64
  • 77

7 Answers7

111

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.

CL.
  • 173,858
  • 17
  • 217
  • 259
Christian Davén
  • 16,713
  • 12
  • 64
  • 77
  • Not sure, as I'm not sure how sqlite internals works. It's worth noting that jle and my suggestions above are how PHP's MDB2 library suppports altering the id. Take a look at their sqlite driver source: http://cvs.php.net/viewvc.cgi/pear/MDB2/MDB2/Driver/sqlite.php?view=markup – dave mankoff Mar 28 '09 at 15:06
  • Integrity isn't the issue (I think, not an expert or anything). The problem I believe they are warning about is if you delete a row with an ID less than MAX(ID) and try and set auto-increment manually this way. You will get stuck after the first inserted row because of a primary key violation. – Assimilater Apr 08 '15 at 06:07
  • The exact algorithm is documented on the linked page, and will not be changed in any 3.x version to preserve compatibility. – CL. May 27 '15 at 17:35
  • Perfect! Easy and elegant solution. – Nesar May 28 '17 at 17:36
  • 2
    The only problem is that this doesn't work if there is not yet an entry for the table in the sqlite_sequence. See iTech's answer for the correct way. – w00t Jan 23 '18 at 21:03
  • This does only work if you use explicit the AUTOINCREMENT keyword in SQLite which is not recommended for standard use cases: "The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed." (cited from your link!). The implicit auto increment feature for an INTEGER PRIMARY KEY column works differently (as alias for ROWID). – schlamar Mar 23 '21 at 10:13
27

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

dave mankoff
  • 17,379
  • 7
  • 50
  • 64
  • 8
    I suppose this is the best way to do it, but it's not pretty. – Christian Davén Apr 03 '09 at 09:37
  • This doesn't work for me. If I have a table "Users" with only 1 entry (id 1), while auto-increment is at 3, then another user is inserted with id -1 then deleted, next inserted user is still id 4 (SQLite manager extension for firefox, if it makes a difference) – Assimilater Apr 08 '15 at 06:01
  • 1
    seems like a workaround. i prefer addressing the sqlite_sequence table record for the table in question. – tony gil Aug 16 '16 at 22:04
24

I am using the below query which solves the problem when the sqlite_sequence does not have a record for the table (i.e. first record was not added yet 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;
iTech
  • 18,192
  • 4
  • 57
  • 80
  • This is the only fully correct solution here. The changes voodoo makes my eyes water, though ;) – w00t Jan 23 '18 at 21:01
  • Why is 'changes()' being used here? Can't you just do: WHERE NOT EXISTS SELECT name from sqlite_sequence WHERE name = 'table' – ndw Mar 14 '19 at 18:34
11

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.

jle
  • 9,316
  • 5
  • 48
  • 67
5

In solution with SQLITE_SEQUENCE table, the entry into this table seems to be added after the first insert into the table with the autoincrement column is added. In some cases this might cause troubles (i.e autoincrement still starts from 1, not from wanted value).

Raivo Laanemets
  • 1,119
  • 10
  • 7
2

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()"
Bernhard
  • 51
  • 3
0

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>'
kamalkavin68
  • 1
  • 1