When a user unsubscribes from my mailing list, I’m trying to reset the auto increment. Manually entering in this doesn’t seem to reset my auto increment.
I have rows 1-8 where PersonID will be 1-8
Then I’ll have row 9 with PersonID = 9.
If I delete 9, next time someone submits to be subscribed, they get ID=10. I know it doesn’t matter but I’d still like to know why the auto increment reset doesn’t work. I coulda sworn it was working a while ago.
Nevermind - this guy fixed it for me. He says “Note that you cannot reset the counter to a value less than or equal to any that have already been used.”
I just set it to auto increment 1 and let the database work it out. Seems to work.
There’s another topic about this (can’t remember the title).
Auto_increment doesn’t “reset” the values when you delete one, shifiting every single id value to there’s no empty spaces, so to say, it is, in general a bad practice. Most of the time, because you may reference that table in some other table. And where there’s a relationship between two tables, chaging the ID is a big deal.
Still, if you really need to do this or you don’t care, simply use an UPDATE to change the values from 10 onwards.