How do you collapse a key-value data stack back onto itself to keep the ID's sequential?

So her开发者_JS百科e is the data structure. It's simplistic, but there's a catch.

INT id, TEXT subject, TEXT response

Basically, it's going to be populated with simple data entries. However, they need to support removal. The catch is, the ID numbers always have to be sequential. eg, 1-100 not skipping around like 1-4,6-12,14-100 because 5 and 13 were deleted.

What I'm asking is, basically, how do you collapse a key-value data stack back onto itself to keep the ID's sequential?

If this was actually a requirement (and something smells fishy about that), I would probably accomplish it with an ON DELETE trigger.

In this trigger, whenever a delete occurred, I would immediately fetch the row corresponding to the current max(id) in the table, and if max(id) is greater than the id of the row you just deleted*, I'd do an update on the existing row's ID to match the ID of the row just deleted.

*The reason to check that max(id) is greater than the id of the row you just deleted, is to make sure you don't do the update in the case where you just deleted the max row.

Now, I don't know if sqlite even supports triggers. If it doesn't, you'll have to implement this same logic in your client code.

Of course this means your ids will fluctuate wildly... which in most applications would be far more problematic than having non-sequential IDs. So I'm still left wondering why you think you need sequential IDs.