Database cache in SQL Or correcting autoincrement

Question

I've created 2 rows in an table in SQL (sqlite3 on cmd) and then deleted 1 of them.

CREATE TABLE sample1( name TEXT, id INTEGER PRIMARY KEY AUTOINCREMENT);
INSERT INTO sample1 VALUES ('ROY',1);
INSERT INTO sample1(name) VALUES ('RAJ');
DELETE FROM sample1 WHERE id = 2;

Later when I inserted another row, its id was given 3 by the system instead of 2.

INSERT INTO sample1 VALUES ('AMIE',NULL);
SELECT * FROM sample1;

picture of table

How do I correct it so the next values are given right id's automatically? Or how do I clear the sql database cache to solve it?

This behavior is by design. Integer primary keys are not row numbers. — forpas, Sep 10 '22 at 08:00
The entire *point* of `AUTOINCREMENT` is to never re-use a rowid value. — Shawn, Sep 10 '22 at 08:26

MikeT · Accepted Answer · 2022-09-11T17:55:26.433

The simplest fix to resolve the problem you describe, is to omit AUTOINCREMENT.

The result of your test would then be as you wish.

However, the rowid (which the id column is an alias of, if INTEGER PRIMARY KEY is specified, with or without AUTOINCREMENT), will still be generated and probably be 1 higher than the highest existing id (alias of rowid).

There is a subtle difference between using and not using AUTOINCREMENT.

without AUTOINCREMENT then the generated value of the rowid and therefore it's alias will be the highest existing rowid for the table plus 1 (not absolutely guaranteed though).
with AUTOINCREMENT the generated value will be 1 plus the higher of:-
- the highest existing rowid, or
- the highest used rowid
- the highest, in some circumstances, may have only existed briefly

In your example as 2 had been used then 2 + 1 = 3 even though 2 had been deleted.

Using AUTOINCREMENT is inefficient as to know what the last used value was requires a system table, sqlite_sequence and it being accessed to store the latest id and also to retrieve the id.

The SQLite AUTOINCREMENT documentation, says this:-

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

There are other differences, such as with AUTOINCREMENT if the id 9223372036854775807 has been reached, then another insert will result in an SQLITE_FULL error. Whilst without AUTOINCREMENT then an unused id (there would be one as current day storage devices could not hold that number of rows).

The intention of id's (rowid's) is to uniquely identify a row and to be able to access such a row efficiently if accessing it by the id. The intention is not for it to be used as a sequence/order. Using it as a sequence/order number will probably invariably result in unanticipated sequences or inefficient overheads trying to maintain such a sequence/order. You should always consider that rows are unordered unless specifically ordered by a clause that orders the output, such as an ORDER BY clause.

However, if you take your example a little further, omitting AUTOINCREMENT, will still probably result in the order/sequence issues as if, for example, the row with an id of 1 were deleted instead of 2 then you would end up with id's of 2 and 3.

Perhaps consider the following which shows a) how the limited issue you have posed, is solved without AUTOINCREMENT, and b) that it is not the solution if it is not the highest id that is deleted:-

DROP TABLE IF EXISTS sample1;
CREATE TABLE IF NOT EXISTS sample1( name TEXT, id INTEGER PRIMARY KEY);
INSERT INTO sample1 VALUES ('ROY',1);
INSERT INTO sample1(name) VALUES ('RAJ');
DELETE FROM sample1 WHERE id = 2;
INSERT INTO sample1 VALUES ('AMIE',NULL);
/* Result 1 */
SELECT * FROM sample1;

/* BUT if a lower than the highest id is deleted */
DELETE FROM sample1 WHERE id=1;
INSERT INTO sample1 VALUES ('EMMA',NULL);
/* Result 2 */
SELECT * FROM sample1;

Result 1 (your exact issue resolved)

Result 2 (if not the highest id deleted)

Database cache in SQL Or correcting autoincrement

1 Answers1