Andy Andy - 6 months ago 28
MySQL Question

Autoincrement not working properly in table

I created a table

department
with 4 columns and set
deparmentid
column to autoincrement. Now, after deleting 8 records out of 10, on adding the new record the value of
departmentid
is shown as 11 instead of 3. I truncated the whole table but again it is showing the same result on inserting the data. What should I do?

Answer

You can reset the counter with:

ALTER TABLE tablename AUTO_INCREMENT = 1

For InnoDB you cannot set the auto_increment value lower or equal to the highest current index.