0

Lets say I have a table which has a primary key of 1 to X. During the development of the table there are entries which have been deleted, say I deleted entry 5. And hence the table entry will be 1,2,3,4,6,7,8. Is there a query to find all id of the primary key which has been skipped?

adit
  • 32,574
  • 72
  • 229
  • 373
  • Same as http://stackoverflow.com/questions/11103016/sql-query-to-get-deleted-records –  Aug 25 '14 at 06:34
  • One way is to join with a numbers table and find those not in the source table. You would need to know the maximum PK in your table. – shree.pat18 Aug 25 '14 at 06:34
  • @shree.pat18 yea the maximum PK as of speaking now is at 5000 – adit Aug 25 '14 at 06:38
  • May I ask why it matters, why do you care that there are 'gaps' in the ids? – Mark Rotteveel Aug 25 '14 at 14:45
  • Another one that thinks these numbers are sequential.. they're not. Primary key and auto_increment serve **one purpose** - to provide unique identifier for a row. They don't provide you with sequential numbers, they are not called gaps, you shouldn't reuse them because it creates performance and concurrency problems and makes your data model unstable. TL;DR - just don't do it. – N.B. Aug 25 '14 at 14:47

2 Answers2

0

Take a look at this link, it have a solution to your problem

Quoted from the link:

select l.id + 1 as start
from sequence as l
  left outer join sequence as r on l.id + 1 = r.id
where r.id is null;

Supposing you have a table called sequence with primary key column Id starting from 1, With values: 1,2,3,4, 6,7, 9,...

This sample code will select 5 and 8.

sameh.q
  • 1,691
  • 2
  • 23
  • 48
0

simsim's answer will not return all missing keys if 2 keys in a sequence are missing. SQLFiddle demo: http://sqlfiddle.com/#!2/cc241/1

Instead create a numbers table and join where the key is null:

CREATE TABLE Numbers(
    Num INTEGER
)

DECLARE @id INTEGER
SELECT @id = 1
WHILE @id >=1 AND @id <= 100000
BEGIN
    INSERT INTO Numbers
    VALUES(@id)
    SELECT @id += 1
END

SELECT *
FROM Numbers N
LEFT JOIN your_table YT ON N.Num = YT.PrimaryKey
WHERE YT.Primary IS NULL
craM 4D-61-72-63
  • 43
  • 5