sqlite tracking IDs - find missing integers in a seq

Question

First I am not even sure whether I am asking the right question, sorry for that. SQL is new to me. I have a table I create in SQLITE like this:

CREATE TABLE ENTRIES "(ID INTEGER PRIMARY KEY AUTOINCREMENT,DATA BLOB NOT NULL)"

Which is all fine if I have only additions for entries. If I create entries, they increment. Let us say I added 7 entries. Now I delete 3 entries:

DELETE FROM NODES WHERE ID = 3
DELETE FROM NODES WHERE ID = 4
DELETE FROM NODES WHERE ID = 5

Entries I now have are:

1,2,6,7.

The next time I add an entry it will have ID=8.

So, my question is:

How do I get the next 3 entries, to get the IDs 3, 4, 5 and only the 4 entry will then get 8? I realize this is similar to SQL: find missing IDs in a table, and it is maybe also a general programming (not just SQL) problem. So, I would be happy to see some Python and SQLite solutions.

Thanks, Oz

Why do you need this? It's common to leave auto-incrementing id generation to the database, with the understanding that gaps can occur. There are down-sides to re-using now-deleted ids, etc. — Martijn Pieters, Oct 08 '12 at 15:48

score 0 · Answer 1 · answered Oct 08 '12 at 15:47

I don't think that's the way auto incrementing fields work. SQLite keeps a counter of the last used integer. It will never 'fill in' the deleted values if you want to get the next 3 rows after an id you could:

SELECT * FROM NODES WHERE ID > 2 LIMIT 3;

This will give you the next three rows with an id greater than 2

Additionally you could just create a deleted flag or something? so the rows are never actually removed from your database.

leoluk · Answer 2 · 2012-10-08T16:17:40.257

You can't. SQLite will never re-use deleted IDs, for database integrity reasons. Let's assume you have a second table which has a foreign key which references the first table. If, for some reason, a corresponding row is removed without removing the rows which reference it (using the primary ID) as well, it will point to the wrong row.

Example: If you remove a person record without removing the purchases as well, the purchase records will point to the wrong person once you re-assign the old ID.

    ─────────────────────     ────────────────────
     Table 1 – customers       Table 2 – purchase
    ─────────────────────     ────────────────────

     *ID     <───────┐        *ID
      Name           │         Item
      Address        └───────  Customer
      Phone                    Price

This is why pretty much any database engine out there assigns primary IDs strictly incremental. They are database internals, you usually shouldn't touch them. If you need to assign your own IDs, just add a separate column (think twice before doing so).

If you want to keep track of the number of rows, you can query it like this: SELECT Count(*) FROM table_name.

sqlite tracking IDs - find missing integers in a seq

2 Answers2