CODEWITHSUNDEEP
databasesqlite
raz
raz

Reputation: 492

Is it possible to determine irregularity in the primary key of SQLite Database

I have a Table in SQLite Named UserAccessLevels, and Column name of the primary key is No. The sequence of the primary key is 0,1,3,4 in the primary key 2 is missing. Is it possible to find this irregular primary key 2 by running any query in SQLite Database.

Upvotes: 0

Views: 54

Answers (1)

CL.
CL.

Reputation: 180060

This can be done with a correlated subquery to check for the existence of the respective next row (with the special case of the last row):

SELECT No + 1
FROM UserAccessLevels
WHERE NOT EXISTS (SELECT 1
                  FROM UserAccessLevels AS T2
                  WHERE T2.No = UserAccessLevels.No + 1)
  AND No != (SELECT MAX(No)
             FROM UserAccessLevels);

(When there is a gap larger than one, this returns only the start of the gap.)

Upvotes: 2

Related Questions