Set start value for AUTOINCREMENT in SQLite


up vote
11
down vote
favorite

share [g+]
share [fb]
share [tw]

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

link|edit|flag
79% accept rate

4 Answers

active
oldest
votes

up vote
5
down vote
accepted

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

link|edit|flag
upvote
flag
I suppose this is the best way to do it, but it's not pretty. –Christian Davén Apr 3 '09 at 9:37

up vote
10
down vote

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.

Could this be dangerous to the integrity of my database?

link|edit|flag
upvote
flag
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: cvs.php.net/viewvc.cgi/pear/MDB2/MDB2/Driver/… –dave mankoff Mar 28 '09 at 15:06

up vote
3
down vote

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.

link|edit|flag

up vote
0
down vote

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).

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.