Autoincrement not working properly in table

Question

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?

Possible duplicate of [How to reset AUTO\_INCREMENT in MySQL?](http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql) — Giacomo Garabello, Jun 07 '16 at 06:37

score 2 · Accepted Answer · answered Jun 07 '16 at 06:38

2

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.

answered Jun 07 '16 at 06:38

aarju mishra

710
3
10

Welcome.You can mark my answer as correct.Thanks in advance. – aarju mishra Jun 07 '16 at 06:53

score 0 · Answer 2 · answered Jun 07 '16 at 06:36

0

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;

answered Jun 07 '16 at 06:36

Vasil Rashkov

1,818
15
27

Autoincrement not working properly in table

2 Answers2