In this tutorial, we will show you how to reset the Auto increment field in MySQL.
Mysql Auto-increment allows a unique number to be generated when a new record is inserted into a table. The function allows you to automatically add 1 to the previous record if you enter a new value.
You can always reset Mysql auto increment so that when you delete a record your data does show an uneven picture.
Step 1 – Selecting records
Over here, we will be using the MySQL instance called SQL Yog.
Just consider this table having the names of animals and the serial number, in this example the Mysql auto increment function is in operation.
If we delete any of the record from the table, that particular ID value will not be associated with any other field. This is because the auto number field will not change its order according to the changes made in the table.
Step 2 – Selecting the column
To fix this problem, you can simply drop the column and make it once again.
For that, we will write the alter table query along with the table name and specify the id column with the drop keyword.
When we run the query and fetch all the records from the table, you will notice that the ID field has been deleted.
Now let’s type the Alter Table query and give the table name which in our case is animals. Next, we will include the ADD keyword along with the column name which is ID in this case. Next, we will specify the data type, which would be Medium Integer in this case and after that, let’s add the Not Null and Auto increment Constraints along with the Primary key.
With that done, let’s run the query and fetch all the records from the database.
With that done, you will notice that all Auto increment field has been reset, and all the ID’s are now in order.
And in this manner you can reset Mysql auto increment.
Home