Continuous increase in database id sqlite

Question

This is how I create a database.

CREATE TABLE $_tableName(
        id INTEGER NOT NULL PRIMARY KEY,
        category TEXT,
        content TEXT,
        date TEXT
        )

When I delete any data, id continues from where it left off. For example, I have id data in the range 1-10. When the content number 3 is deleted from the database, the newly added value id becomes 11. I want to save the new value in the blank 3 field.

  Future<int> add(NoteDbModel saveDbModel) async {
    Database db = await instance.database;
    return await db.insert(_tableName, saveDbModel.toMap());
  }

  Future<int> remove(int id) async {
    Database db = await instance.database;
    return await db.delete(_tableName, where: 'id = ?', whereArgs: [id]);
  }

Answer 1

The intended purpose of an id is to uniquely identify a row.

SQLite treats tables, other than virtual tables and WITHOUT ROWID tables, that have a column defined as INTEGER with PRIMARY KEY in a special way. The column is an alias of what could be classed as the SUPER KEY, that is it is an alias of the hidden rowid column.

SQLite favours (is optimised for) use of the rowid or an alias thereof. When scanning/searching the underlying index that scan/search can be up to twice as fast.

As you have found if, when inserting a row with such a column, and that column is provided a value, then the value gets generated. It is simply along the lines of max((SELECT id FROM theTable))+1.

So if any but the last row is deleted then that value remain unused (see the following notes as this is not actually the whole truth).

• unless the highest ever possible value gets used, when SQlite will attempt to find an unused BUT lower value
  • unless AUTOINCREMENT is used coded when instead an SQLITE_FULL error is raised.
    • AUTOINCREMENT for SQLite is inefficient and does not determine the generation of a monotonically increased generated value.

Trying to use the column for any other purpose, such as wanting it to be specific values to suit a human, will often result in issues, such as the one you have raised.

Demo (including using insert to insert with first unused id, even though not recommended)

• note just the bare SQL, which could be adapted

:-

/* Just in case demo environment was not cleaned up */
DROP TABLE IF EXISTS demo;

/* The demo table */
CREATE TABLE IF NOT EXISTS demo (id INTEGER NOT NULL PRIMARY KEY, another_column TEXT DEFAULT 'whatever');

/* Add some data (ids 1 -10 in all likeliehood) */
WITH 
    cte(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cte LIMIT 10)
    INSERT INTO demo (another_column) SELECT 'somedata' FROM cte
;
SELECT rowid,* FROM demo; /* RESULT 1 - Original data */

/* Drop odd numbered ids */
DELETE FROM demo WHERE id % 2;
SELECT rowid,* FROM demo; /* RESULT 2 - After deleting odd # ids */

/* replicate issue i.e. add 7 new rows (ids 11-17) */
WITH 
    cte(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cte LIMIT 7)
INSERT INTO demo (another_column) SELECT 'newdata #'||x FROM cte;
SELECT rowid,* FROM demo; /* RESULT 3 - ids not filling free/unused ids */

/* Revert to original with dropped odd ids */
DELETE FROM demo WHERE id > 10;
SELECT rowid,* FROM demo;

/* Insert new row using the first unused id, if one */
WITH
    cte_extraValueToInsert(newValue) AS (SELECT 'the new data to be inserted 1'),
    cte(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cte LIMIT (SELECT max(id) FROM demo)),
    cte_missing(expected) AS (SELECT x FROM cte WHERE (SELECT id FROM demo WHERE demo.id=x) IS NULL ORDER BY x ASC LIMIT 1)
    INSERT INTO demo (id,another_column) VALUES(
        (CASE 
            WHEN (SELECT expected FROM cte_missing) 
            THEN (SELECT expected FROM cte_missing) 
            ELSE (SELECT max(id)+1 FROM demo) 
        END),
        (SELECT * FROM cte_extraValueToInsert)
        )
;

/* Insert second new row using the first unused id, if one */
WITH
    cte_extraValueToInsert(newValue) AS (SELECT 'the new data to be inserted 2'),
    cte(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cte LIMIT (SELECT max(id) FROM demo)),
    cte_missing(expected) AS (SELECT x FROM cte WHERE (SELECT id FROM demo WHERE demo.id=x) IS NULL ORDER BY x ASC LIMIT 1)
    INSERT INTO demo (id,another_column) VALUES(
        (CASE 
            WHEN (SELECT expected FROM cte_missing) 
            THEN (SELECT expected FROM cte_missing) 
            ELSE (SELECT max(id)+1 FROM demo) 
        END),
        (SELECT * FROM cte_extraValueToInsert)
        )
;
SELECT rowid,* FROM demo;

/* Clean up DEMO environment */
DROP TABLE IF EXISTS demo;

• cte's are Common Table Expressions, they are effectively a temporary table with a lifetime as per the execution of the SQL/Query
  • they can even be used for recursion (some of the cte's in the code are recursive). You may wish to refer to https://www.sqlite.org/lang_with.html

Demo Results

1. After initial population of row rows with generated ids:-

2. After deletion of odd ids

3. After adding 7 rows with generated ids (i.e. the issue) i.e. WRONG

4. After deleting the WRONG inserts (back to 2))

5. After inserting two rows using the fix(sic) SQL

• Note that this is just an in-principle demo, it has not been exhaustively tested (e.g. using it when there are no unused). Use of this is not recommended.