👉 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 once you create yourself so 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
Also, it's advised not to mess with the entries as the table is used my 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
With the error letting us know the sqlite_sequence does not exist we need to create it.
But since we can't create one using the
CREATE command, we will have to trigger for 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.