Why does SQLite create empty sqlite_sequence table?

Question

I have a database of several tables in which the first field is defined as:

'ID' INTEGER NOT NULL UNIQUE ... PRIMARY_KEY('ID')

I do not use the AUTOINCREMENT keyword for any of these tables yet my db contains an empty sqlite_sequence table which I cannot delete. Inserting records into these tables with a NULL value for the ID field generates default incremental values for the ID fields but does not populate or update the sqlite_sequence table. Is there any way I can reset the last inserted rowid value to 0 for these tables?

Candicecandid

When you define an integer column as PRIMARY_KEY it is also AUTOINCREMENT. Read more here: sqlite.org/autoinc.html – Wren

UNIQUE on a primary key column is redundant and pointless, btw. – Komsomol

Did you have a table with an AUTOINCREMENT integer PK at some point and then drop it? – Komsomol

@Shawn, regarding UNIQUE, point taken. Regarding previously existing but now dropped table, I can't recall but if no longer "active" and empty why can't I delete it? – Candicecandid

It's an internal table (as are all that start with sqlite_ and explicitly cannot be dropped once the system creates it. sqlite.org/fileformat2.html#the_sqlite_sequence_table – Komsomol

Perhaps I should rephrase my original question: How do I reset the last inserted rowid to 0 for a table in a db where sqite_sequence is absent or empty and not used? – Candicecandid

i believe a simple UPDATE command could do it but resetting/altering a sequence is most of the time a bad idea.. – Clevey

In a rowid table without AUTOINCREMENT, a new rowid is calculated based on the current maximum rowid present in the table. See the link that forpas provided for a complete description of the algorithm. tl;dr: reset it by deleting all rows from the table. – Komsomol

Answer 1

According to your SQL below, I think you created sqlite_autoindex_... table with UNIQUE instead of sqlite_sequence table with AUTOINCREMENT. *sqlite_sequence table is never ever created unless you create a table with an AUTOINCREMENT column.:

ID' INTEGER NOT NULL UNIQUE ... PRIMARY_KEY('ID')

The doc says below:

Do not confuse automatic indexes with the internal indexes (having names like "sqlite_autoindex_table_N") that are sometimes created to implement a PRIMARY KEY constraint or UNIQUE constraint.

For example, you create test table which has id column with TEXT PRIMARY KEY or TEXT UNIQUE as shown below:

CREATE TABLE test (
  id TEXT PRIMARY KEY
);

Or:

CREATE TABLE test (
  id TEXT UNIQUE
);

Then, sqlite_autoindex_test_1 table is created as shown below. *My answer explains how to show column names:

sqlite> .headers on
sqlite> .mode box
sqlite> SELECT name FROM sqlite_master;
┌───────┬─────────────────────────┐
│ type  │          name           │
├───────┼─────────────────────────┤
│ table │ test                    │
│ index │ sqlite_autoindex_test_1 │
└───────┴─────────────────────────┘

In addition, you create test table which has id column with INTEGER PRIMARY KEY as shown below:

CREATE TABLE test (
  id INTEGER PRIMARY KEY
);

Then, sqlite_autoindex_test_1 table is not created as shown below.

sqlite> .headers on
sqlite> .mode box
sqlite> SELECT type, name FROM sqlite_master; 
┌───────┬──────┐
│ type  │ name │
├───────┼──────┤
│ table │ test │
└───────┴──────┘

But, you create test table which has id column with INTEGER UNIQUE as shown below:

CREATE TABLE test (
  id INTEGER UNIQUE
);

Then, sqlite_autoindex_test_1 table is created as shown below:

sqlite> .headers on
sqlite> .mode box
sqlite> SELECT name FROM sqlite_master;
┌───────┬─────────────────────────┐
│ type  │          name           │
├───────┼─────────────────────────┤
│ table │ test                    │
│ index │ sqlite_autoindex_test_1 │
└───────┴─────────────────────────┘

Diatom

Answer 2

Do not use AUTOINCREMENT in create table script

Sacerdotalism

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Four