Auto Increment after delete in MySQL

What you're trying to do sounds dangerous, as that's not the intended use of AUTO_INCREMENT.

If you really want to find the lowest unused key value, don't use AUTO_INCREMENT at all, and manage your keys manually. However, this is NOT a recommended practice.

Take a step back and ask "why you need to recycle key values?" Do unsigned INT (or BIGINT) not provide a large enough key space?

Are you really going to have more than 18,446,744,073,709,551,615 unique records over the course of your application's lifetime?

Try :

SET @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE tableName AUTO_INCREMENT = 1;

That'll reset the autoincremented value, and then count every row while a new value is created for it.

example : before

• 1 : first value here
• 2 : second value here
• X : deleted value
• 4 : The rest of the table
• 5 : The rest of the rest..

so the table will display the array : 1,2,4,5

Example : AFTER (if you use this command you will obtain)

• 1 : first value here
• 2 : second value here
• 3 : The rest of the table
• 4 : the rest of the rest

No trace of the deleted value, and the rest of the incremented continues with this new count.

BUT

1. If somewhere on your code something use the autoincremented value... maybe this attribution will cause problem.
2. If you don't use this value in your code everything should be ok.

Primary autoincrement keys in database are used to uniquely identify a given row and shouldn't be given any business meaning. So leave the primary key as is and add another column called for example courseOrder. Then when you delete a record from the database you may want to send an additional UPDATE statement in order to decrement the courseOrder column of all rows that have courseOrder greater than the one you are currently deleting.

As a side note you should never modify the value of a primary key in a relational database because there could be other tables that reference it as a foreign key and modifying it might violate referential constraints.

ALTER TABLE foo AUTO_INCREMENT=1

If you've deleted the most recent entries, that should set it to use the next lowest available one. As in, as long as there's no 19 already, deleting 16-18 will reset the autoincrement to use 16.

---

EDIT: I missed the bit about phpmyadmin. You can set it there, too. Go to the table screen, and click the operations tab. There's an AUTOINCREMENT field there that you can set to whatever you need manually.