[RESOLVED] What Am I Doing Wrong?

[brendan2b]

I have a table full of information corresponding to usernames. Each row has a unique id number starting at 1 and going to 9999. Why is it that when i delete a row and insert a new one, the new row gets inserted above the one I just deleted. In other words if I have rows

38
39
40

And i delete 38, and add another row it becomes

41
39
40

This messes up in my code when try to return some data and I was wondering if this was a common problem.

[Teach]

How did you resolve this, if you don't mind?

That annoys me too, that ID's aren't reused if removed.
We have a user database, and if a user is deleted, it would be better if there is an empty ID it uses that one...instead of just following on from the last one. At the moment, we end up with things like

1
2
10
11
12
19
20

etc

[bradgrafelman]

I don't mind missing ID's at all... why waste the CPU/RAM to calculate missing ID's? They're just reference numbers. shrug

Otherwise, you'd have to run a query that orders the table on the ID column with ascending values, and start looping through all of the ID's until the last ID + 1 doesn't equal the current ID retrieved from the loop.

[Weedpacket]

And if the new record refers to a different entity from the old record, why should it have the same ID number?