Is there a way to determine whether a table, a very large table possibly, has contiguous / consecutive auto increment primary key IDs? Is there a SQL query way to determine this? Suppose someone deletes some rows randomly from a very large table. I need to know that this has happened.
e.g.
table XYZ
id
1
2
3
4
table abc
1
2
4 <--- non contiguous, skipped 3
5
Curious about data integrity. I want a SQL query methodology way in order to just keep things simple and not have to write a PHP script to run against the database.
You could compare these two values:
SELECT (MAX(ID) - MIN(ID)) + 1, -- e.g. ID 2 - ID 1 = 1 (+1) = 2 rows
COUNT(ID)
FROM Table
If the table is still contiguous they will be the same.
How about this: count the number of rows, subtract the lowest ID from the highest, and if the two numbers match then the IDs are contiguous.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With