Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Determine If Table Has Consecutive Primary ID Auto Increment Values

Tags:

sql

php

mysql

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.

like image 874
FinalForm Avatar asked Aug 26 '11 14:08

FinalForm


2 Answers

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.

like image 86
Yuck Avatar answered Oct 20 '22 17:10

Yuck


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.

like image 36
Ciaran Keating Avatar answered Oct 20 '22 19:10

Ciaran Keating