SQLite Forum

Resetting id count

Resetting id count

(1) By Cecil (CecilWesterhof) on 2020-07-05 22:18:00 [link] [source]

I am playing with SQLite as described in "Main Topic and Non Main Topics".

I have to try out a lot. For this I need to delete the old tables.
I could do that with:
    DROP TABLE [IF EXISTS] [schema_name.]table_name;

The problem with this is that it does not reset the id count of the primary keys.
In this case not a problem, I just throw away the complete db and start with a new one.
But what if that is not an option? How can I reset the id count?

(2) By Richard Hipp (drh) on 2020-07-05 22:31:14 in reply to 1 [link] [source]

Are you asking about the AUTOINCREMENT values for the INTEGER PRIMARY KEY?

You reset that by clearing the entry from the "sqlite_sequence" table:

   DELETE FROM [schema_name.]sqlite_sequence WHERE name='table_name';

(3) By Cecil (CecilWesterhof) on 2020-07-05 22:47:55 in reply to 2 [link] [source]

Yes, that was what I mend.
I am going to try it out.

(4) By Cecil (CecilWesterhof) on 2020-07-05 23:17:09 in reply to 2 [link] [source]

If I try:
    SELECT * FROM sqlite_sequence;
I get:
    Error: no such table: sqlite_sequence

I am working with:
    SQLite version 3.27.2

The table sqlite_master does exist.

(5) By Rowan Worth (sqweek) on 2020-07-06 03:47:01 in reply to 4 [source]

Probably you haven't created any tables that use AUTOINCREMENT columns in that database yet.

(8) By Cecil (CecilWesterhof) on 2020-07-06 09:22:24 in reply to 5 [link] [source]

You are correct. I thought the problem I initially had was with the id's, but I tried it again and now I do not have a problem.
Strange, but problem solved.

(6.1) By Keith Medcalf (kmedcalf) on 2020-07-06 04:07:33 edited from 6.0 in reply to 1 [link] [source]

This is impossible to occur unless you specifically and with intention aforethought defined the INTEGER PRIMARY KEY column with the attribute AUTOINCREMENT. If you did that, then this behaviour is EXACTLY the specific reason that the AUTOINCREMENT keyword exists in the first place.

If you did not want this behaviour then you should not have used the AUTOINCREMENT keyword. Just because it "sounds cool" does not mean that it "works cool". It has a very specific meaning and very specific purpose. Unless you need to "carry out" the specific purpose, do not use the command for carrying out that purpose.

Using a command because it sounds cool is kind of like ordering a firing squad to "fire" and then wondering why there are dead people piling up all over the place. What did you think would happen?

Either that of you are not dropping the tables that you think you are dropping.

(7) By Cecil (CecilWesterhof) on 2020-07-06 09:19:50 in reply to 6.1 [link] [source]

You are right. Dropping the views and tables and creating them anew does not give a problem. Probably the error I first got was a different one.
Well problem solved. In my case it does not matter, but dropping and recreating I find a much better option as deleting and recreating the database.