0

Instead of automatically adding the ID number after the last highest ID number, how can I:

Automatically add the next available blank in sequence.

ie

I have a table with the following ID's that are taken

1,2,5,9

Instead of auto incrementing so the next one is 10, I'd like to know if possible I can automatically make the next ID 3.

I have many ID's that need filling before I start adding more to the end.

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
Mark
  • 25
  • 6
  • 2
    `autoincrement` won't do this easily. You could write a query to do this, but ask yourself: why does it matter? –  Feb 14 '15 at 05:40
  • Why do you want to do this at all? – H Aßdøµ Feb 14 '15 at 05:48
  • Don't reuse removed IDs, it's way to hell. – pavel Feb 14 '15 at 05:49
  • 1
    Do you expect a gap-less primary key column? What happens, if you have rows numbered from 1 to 20 and you delete rows from 5 to 10? This leaves a gap in between across the sequence. Take a reasonable data type like `BIGINT` instead and never worry about it. It is not going to be exhausted as long as your project/application lives, if you might fear of (it may take million years to exhaust the sequence). – Tiny Feb 14 '15 at 05:52
  • Hi, ID's havent been used before panther but I hear you if they had been. Interesting one Tiny thanks, @HoboSapiens @H Aßdøµ it matters just because other wise I could reach the maximum length of integer. Probably what I can do is change of the existing ID's around a bit so the autosequencing is back on track. – Mark Feb 14 '15 at 06:39
  • who are these buckwheats that mark these questions down? what was wrong with this question? – Mark Feb 14 '15 at 06:41
  • 3
    "*The max bigint is 9223372036854775807 . At 1000 inserts/second that's 106751991167 days worth. Almost 300 million years, if my maths is right.*" http://stackoverflow.com/a/13133035/1391249 – Tiny Feb 14 '15 at 06:47
  • might be enough then – Mark Feb 15 '15 at 09:25

0 Answers0