sqlite3: Unique constraint

Question

I am trying to create a new database with Table_A.

Table_A

id name comments key

1 peter random string 1234

2 sam random string 5678

This is the schema:

CREATE TABLE IF NOT EXISTS Table_A (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name VARCHAR(60) NOT NULL,
class TEXT NOT NULL DEFAULT '',
key INTEGER NOT NULL,
UNIQUE(name, key) ON CONFLICT IGNORE);

Now when I try to insert elements to this table no duplicate entries are inserted which is expected because of

UNIQUE(name, key) ON CONFLICT IGNORE);

But the id is incremented and I am not sure how to fix this.

Actual output:

Table_A
id  name    comments                key
1   peter   random string           1234
26  sam     random string           5678
75  john    random string           1478

Expected output:

Table_A
id  name    comments                key
1   peter   random string           1234
2  sam     random string            5678
3  john    random string            1478

Answer 1

As documented in the documentation, you get autoincremented values without gaps for deleted entries when you drop the AUTOINCREMENT keyword.