How to Find Missing Auto-Incremented IDs in an SQLite Table?

Use a missing value query to find missing IDs in a table

This query is designed to identify missing IDs in a table with an auto-incrementing unique primary key field, where deleted entries may have created gaps in the ID values. For example, a table with the following data:

<code> ID  | Value    | More fields...</code>

---

2 | Cat | ...
3 | Fish | ...
6 | Dog | ...
7 | Aardvark | ...
9 | Owl | ...
10 | Pig | ...
11 | Badger | ...
15 | Mongoose | ...
19 | Ferret | ...

The expected result will be a list of missing IDs:

<code> ID</code>

---

1
4
5
8
12
13
14
16
17
18

We make three assumptions: the initial first ID is 1, the maximum ID is the last ID, and there are no deleted IDs after the current last ID.

For SQLite, a simple solution exists:

<code>SELECT DISTINCT id +1
FROM mytable
WHERE id + 1 NOT IN (SELECT DISTINCT id FROM mytable);</code>

This query effectively identifies missing IDs by generating a sequence of numbers from the first ID to the last ID and excluding existing IDs, leaving missing values. This is a straightforward and efficient way to solve this problem in SQLite.

The above is the detailed content of How to Find Missing Auto-Incremented IDs in an SQLite Table?. For more information, please follow other related articles on the PHP Chinese website!