Table of contents
What is the sqlite_sequence table for?
The sqlite_sequence table keeps track of AUTOINCREMENT columns for tables you create. It's a table like the one you create yourself which means you can UPDATE, INSERT, and, DELETE entries like you would any table.
The only thing you cannot do is to create the sqlite_sequence using the CREATE command.
Also, it's advised not to mess with the entries as the table is used by the database engine.
Why this error?
By default your database will not have the sqlite_sequence table, It's only created the first time you create a new table with an AUTOINCREMENT column.
So for example, when you convert a MySQL database to SQLite your AUTOINCREMENT field may not be transferred over and hence SQLite will not create the sqlite_sequence table because there are no AUTOINCREMENT columns to keep track of.
So when you attempt to drop a table, for example, SQLite will also attempt to delete the entry for that table in the sqlite_sequence table which will lead to the no such table: sqlite_sequence error if the sqlite_sequence table was never created.
There are other DB actions like changing the schema of the AUTOINCREMENT column that could lead to this error as well.
How to fix it
The error hints at sqlite_sequence not existing so we need to create it.
But since we can't create one using the CREATE command, we will have to trigger one to be created.
To do this we will create a dummy table with an AUTOINCREMENT column.
01: CREATE TABLE dummy (
02: id INTEGER PRIMARY KEY AUTOINCREMENT
03: );This should create the dummy table as well as the sqlite_sequence table.
Since we don't need the dummy table we can go ahead and delete it right after.
01: drop table dummy;Now you should have a sqlite_sequence table.
Here is another article you might like 😊 "Diary Of Insights: A Documentation Of My Discoveries"