Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a table, many rows have been deleted, how to get the id of missing rows for next INSERT?

For example

id      col1
1       1
3       3
4       4
5       5
8       8
9       9

how can I get the value of first available id for next INSERT. Here I want to get the id = 2.

Something like

SELECT id+1 FROM table WHERE CLAUSE // pointing to the least available value
// or x = id +1 (after SELECT)
INSERT INTO table (id, ....) VALUES ('x', ....)
share|improve this question
1  
Why do you think you need this? Why is having gaps in the id sequence a problem? –  ypercube Sep 17 '13 at 8:29

2 Answers 2

up vote 4 down vote accepted

If the column is just a surrogate key and is not used for other purposes (display sorting and so forth) then I would just use your database's AUTOINCREMENT equivalent and ignore the fact that there will be gaps in the IDs, as there point is to be unique rather than serving other purposes. The much recommended "SQL Anti-Patterns" book has a chapter on this entitled "pseudokey neat-freak". This depends on what you are modelling and how of course, if the numbers do carry meaning beyond being an identifier then this point is irrelevant.

The general concept you want to read around (it is worth a little background reading as these problems crop up all over the place so knowing how to spot them and deal with them efficiently or design them out is very useful) is gaps and islands. There are many online references about this (this is the first one a quick search found, it is talking about MSSQL but the concepts are transferable).

As well as the WHERE NOT EXISTS method, which is usually suggested as the way the code reads makes your intentions more obvious, you can also do:

SELECT TOP 1 t1.id-1
FROM     yourtable t1
LEFT OUTER JOIN 
         yourtable t2 
ON       t2.id=t1.id-1 -- will match if there is a row with the next ID down from the row in t1
WHERE    t2.id IS NULL -- the next ID down not found
AND      t1.id > 0     -- assume 1 is the lowest valid ID
ORDER BY t1.id

(that is MSSQL syntax, you may need to tweak it)
Both variants should produce similar query plans so perform the same, but I've seen the above approach perform better as part of a more complex query so it may be worth trying both in your circumstances and verifying which performs best.

Also not that neither method as currently presented will return the first ID if the table is empty: you'll get no rows back instead (or NULL if you are using this as a sub-query). One method of getting around this is to have an "invalid" row with id=0 but that is quite dirty (you end up having to filter that out of many other queries) so dealing with the nothing/NULL in your logic instead instead is strongly recommended. For tables that will never be empty in production (a users table for instance, even on initial install there will be one record for the initial setup/admin user) this or course is not an issue.

edit: Corrected the query to return the first ID if not found, as ypercube noted the first version would not do

share|improve this answer
    
In MySQL you can use LIMIT (after ORDER BY) instead of TOP. If the missing id values are in the beginning (1,2,3...), the query will not show 1 as answer, right?. –  ypercube Sep 17 '13 at 10:08
    
Good point, that will never find the first number in sequence. Also it will just return NULL if the table is empty instead of returning the first valid number. –  David Spillett Sep 17 '13 at 13:16
    
I've edited the query to fix that. Also it (sill) will just return nothing (therefore NULL if used in a sub-query) if the table is empty, rather than returning the first valid number (the other examples have the same issue). –  David Spillett Sep 17 '13 at 13:25

You can get a first available id with a query like this

SELECT id + 1 available_id
  FROM table1 t
 WHERE NOT EXISTS
(
  SELECT * 
    FROM table1
   WHERE id = t.id + 1
)
 ORDER BY id
 LIMIT 1

Output:

| AVAILABLE_ID |
|--------------|
|            2 |

Here is SQLFiddle demo


To insert

INSERT INTO table1 (id, col1, ...)
SELECT id + 1, <some_value>, ...
  FROM table1 t
 WHERE NOT EXISTS
(
  SELECT * 
    FROM table1
   WHERE id = t.id + 1
)
 ORDER BY id
 LIMIT 1;

Here is SQLFiddle demo

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.