I’ve recently had need to reuse the primary key for a table whenever a row gets deleted, for example if a row of ID 5 gets deleted, the next entry will re-use the ID of 5. Usually this causes problems with foreign keys, but as no other table had a foreign key linked to this table, it would cause no problems.
The biggest hurdle was finding what the first missing primary key actually is. This takes a bit of SQL, but the following almost gets it:
SELECT MIN(table.id + 1) AS nextID FROM table LEFT JOIN table t1 ON table.id + 1 = t1.id WHERE t1.id IS NULL
I say almost, because if row/id 1 is deleted, the above code won’t catch it. I’m afraid that you’ll have to use
SELECT to check for this:
SELECT count(id) FROM table WHERE id = 1
If the value is 0, then you know you need to create row id 1.
And that’s it. All you need to do is throw this together into a MySQL function (or a php function if you’re not feeling adventurous), and you have the lowest unused primary key all ready for you to put into your