Alter Auto increment stopped working?

[RyanReese]

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.

$resetIncrement=mysqli_query($connection,"ALTER TABLE Subscribe AUTO_INCREMENT=".$count[0]+1);

$count tallies the number of rows in the database after deletion (which after echo, I know it’s right.)

No errors that I know of.

[RyanReese]

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.

http://stackoverflow.com/a/8923132

</thread>

[molona]

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.

So, if you deleted ID=9 , you would

UPDATE table_name
SET id=id-1
WHERE id > 9

[molona]

OK. Glad that you found your answer