CODEWITHSUNDEEP
mysqlauto-increment
andreimazz
andreimazz

Reputation: 23

reset auto increment id after deletion of last row

I made a table in a mysql database for testing purposes. The id is auto incremented. After doing this(not together)

delete from test where id=4;
alter table test auto_increment = 4;
insert into test(nume) values('dan');

It does not give any errors. But the last id is 5, not 4. Should not this be working?

Upvotes: 0

Views: 10281

Answers (3)

Ivo P
Ivo P

Reputation: 1712

This whole procedure has only use in case you do delete the record with the highest id. Should you delete where id=2, you cannot change the autoincrement id. For whatever reason you do not like to get a gap in the id line at id=4, in case of deleting id =2 there will be a gap. So why mess with the ids when after adding a new record id =5 you would have the same kind of gap.

But what about racing conditions. You delete record id=4. two millisec later I add a record, getting id=5. What will happen to the auto increment id?

What legit reason is there for avoiding these gaps?

Upvotes: 0

Janak Prajapati
Janak Prajapati

Reputation: 886

After delete write this query

ALTER TABLE tablename AUTO_INCREMENT = 1

Upvotes: 2

alandarev
alandarev

Reputation: 8635

Question yourself, whether you need to alter the primary key. In most legitimate cases - no.

This will partially work, once you insert a row, the ID will be 4, but auto_increment will change to 5.

As a result, next row insertion will give you a primary key duplication error.

Upvotes: 0

Related Questions