Search code examples
mysqlauto-increment

Can't set auto-increment value of tables to lower value

I used to be able to set an auto-increment value to lower than the existing one after deleting rows in a table, but now I am unable to do so (I can only set it to higher than the current value). I'm wondering if this is because of upgrading MySQL (5.6.11)/PHPMyAdmin (3.5.8), or whether I should still be able to do this?

Solution

  • From the manual:

    You cannot reset the counter to a value less than or equal to any that have 
already been used. For MyISAM, if the value is less than or equal to the 
maximum value currently in the AUTO_INCREMENT column, the value is reset 
to the current maximum plus one. For InnoDB, if the value is less than the 
current maximum value in the column, no error occurs and the current sequence 
value is not changed.

    So in short, it depends in the storage engine used.