CODEWITHSUNDEEP
mysql
Andy
Andy

Reputation: 43

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?

Upvotes: 0

Views: 1165

Answers (2)

aarju mishra
aarju mishra

Reputation: 710

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.

Upvotes: 2

Vasil Rashkov
Vasil Rashkov

Reputation: 1834

This is how auto increment is working. It doesn't matter if you delete from the table. If you want to change the auto increment id you need to run

ALTER TABLE department AUTO_INCREMENT = 3;

Upvotes: 0

Related Questions