Resetting autoincrement

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Resetting autoincrement

Mike Clark
I found this on Stackoverflow:

DELETE FROM your_table;    DELETE FROM sqlite_sequence WHERE name =
'your_table';


But when I try to run it I get "table not found". Has this been superseded?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Resetting autoincrement

Simon Slavin-3

> On 13 Apr 2018, at 11:16 pm, Mike Clark <[hidden email]> wrote:
>
> DELETE FROM your_table;    DELETE FROM sqlite_sequence WHERE name =
> 'your_table';
>
>
> But when I try to run it I get "table not found". Has this been superseded?

No. What is the primary key on that table ?  If you declared is in the CREATE command what did you make it ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Resetting autoincrement

R Smith-2
In reply to this post by Mike Clark
On 2018/04/14 12:16 AM, Mike Clark wrote:
> I found this on Stackoverflow:
>
> DELETE FROM your_table;    DELETE FROM sqlite_sequence WHERE name =
> 'your_table';
>
>
> But when I try to run it I get "table not found". Has this been superseded?

It has not been superseded, it has never even been the defacto method.
It's a bit of a fudge to reset the Autoincrement value since the SQLite
AUTOINCREMENT constraint on an INTEGER PRIMARY KEY will use the
mentioned "sqlite_sequence" table to decide the next key value.

My guess is, if this did not work for you, that you have NOT used the
AUTOINCREMENT specifier when creating your table, and so your keys will
not recall the last key value - which is bad DB practice, but may be
fine for your use case.

This also means you may still have incrementing integer primary keys,
but they are not kept in said table - which in turn means the primary
key value, once deleted from your data table, may at some point be used
again, whereas if you specifically set the integer primary key to be of
the "AUTOINCREMENT" variety,  a value can never be used again, even
after it was deleted.

If I am right, you can just continue, after clearing the table, and the
incrementing should start from 1 again. If this is not the case, post
again but include your schema to remove the guesswork.

Cheers!
Ryan




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users