CODEWITHSUNDEEP
mysql
Sarath Chandra
Sarath Chandra

Reputation: 77

auto increment primary key with delete in mysql

I have a table with an ID AUTO INCREMENT PRIMARY KEY. When I delete an entry and re-add one, the new entry does not take the ID of the previous one instead it increments again by one.

How to change this behaviour to get the id of deleted record to newely add record?

Upvotes: 0

Views: 193

Answers (2)

Ja͢ck
Ja͢ck

Reputation: 173532

First of all, you shouldn't have to care about this; if you want to be really sure that you don't run out of numbers, use BIGINT UNSIGNED for your primary key instead.

Be warned that doing the below is not recommended.

ALTER TABLE mytable SET AUTO_INCREMENT = 123;

This set the number to be used for the next record at 123, so in your case you would set it to the deleted record's identifier.

Upvotes: 0

juergen d
juergen d

Reputation: 204746

This is intended behaviour and can't be changed.

Don't misuse the primary key as indicator of your record order. You can use another colum for that like a datetime with a default value like current_timestamp

Upvotes: 1

Related Questions