Python SQLite3 auto_increment properly increase even when deleting the max ID

Question

Gday,

I'm starting to work with an SQL Database in python and I want to have multiple tables, where I can reference rows in each other's table by ID. Therefore, I'm using a column "testID integer PRIMARY KEY".

The Column increases in value as desired, but if I delete the row with the maximum ID and then add another entry, it will receive the ID which was set already before. That works because deleting the most recent row leads to a lower maximum ID in the column, which makes sense to me.

I was wondering now, is there a way to have the database memorize every ID that was set earlier and not set the same ID twice, even when the maximum ID is deleted from the database?

MWE to maybe make that clearer:

conn = sqlite3.connect("db_problem.db")
c = conn.cursor()
with conn:
    c.execute("CREATE TABLE test ("
              "testID integer PRIMARY KEY, "
              "col1 integer)")
    c.execute("INSERT INTO test (col1) VALUES (1)")
    c.execute("INSERT INTO test (col1) VALUES (2)")
    c.execute("DELETE FROM test WHERE col1 LIKE 1")
    c.execute("SELECT testID FROM test WHERE col1 LIKE 2")
    print(c.fetchall()) # this stays 2, even tho there is only one row left, which works fine
    c.execute("INSERT INTO test (col1) VALUES (3)")
    c.execute("DELETE FROM test WHERE col1 LIKE 3")
    c.execute("INSERT INTO test (col1) VALUES (4)")
    c.execute("SELECT testID FROM test WHERE col1 LIKE 4")
    print(c.fetchall()) # Here the autoincrement was set to 3 although it is the fourth entry made

Answer 1

From SQLite Autoincrement:

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

So create the table with this statement:

c.execute("CREATE TABLE test ("
          "testID integer PRIMARY KEY AUTOINCREMENT , "
          "col1 integer)")

But there is another part of the documentation which you must consider:

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

The choice is yours.