Determine if the table has consecutive primary auto-increment values

Question

Is there a way to determine if a table, a very large table, can have continuous / sequential automatic incremental primary key identifiers? Is there a SQL query way to determine this? Suppose someone deletes some rows randomly from a very large table. I need to know what happened.

eg table XYZ id 1 2 3 4 table abc 1 2 4 <--- non contiguous, skipped 3 5 

Interested in data integrity. I want a SQL query query methodology to just simplify and not write a PHP script to work with the database.

Answer 1

You can compare these two values:

 SELECT (MAX(ID) - MIN(ID)) + 1, -- eg ID 2 - ID 1 = 1 (+1) = 2 rows COUNT(ID) FROM Table 

If the table is still contiguous, they will be the same.

Answer 2

How about this: count the number of lines, subtract the smallest identifier from the highest value, and if the two numbers match, the identifiers will be continuous.

Answer 3

Use self-join

 SELECT * FROM t t1 LEFT JOIN t t2 ON t2.ID = t1.ID + 1 WHERE t2.ID IS NULL 

If there are no spaces, this returns only one row, for the last element in the table. You can even remove this line from the result set if you want to be smart, but that should give you this idea.

This method is especially useful if you want to work more with breaks. If not, the counting methods suggested by others are simpler.