how to prevent auto increment from skipping id numbers in a mysql database?

Question

Okay, so let's say I have a mysql database table with two columns, one is for id and the other is for password. If I have three rows of data and the id values go from 1 to 3 and I delete row 3 and then create another row of data, I will see id=4 instead of id=3 on the newly created row. I know this has to do with the auto increment value but I was wondering if I can add some code in a php file that will automatically reset all the id numbers such that you start at id=1 and go up to the last id number in increments of 1 after a row has been deleted?

My goal is to create a form where the user enters a password and the system will match the password with a password value in the database. If there is a match, the row with the matched password will be deleted and the column of id numbers will be reordered such that no id numbers are skipped.

Update: I'm making a rotating banner ad system by setting a random number from 1 to 4 to a variable so that the php file will retrieve a random ad from id=1 to id=4 by using the random number variable. If the random number happens to be 3 and id=3 does not exist, there will be a gap in the row of banner ads. If there is a way to work around big gaps in this situation, please tell me. thanks in advance

Answer 1

Just execute the following SQL query:

ALTER TABLE `tbl_name` AUTO_INCREMENT = 1;

…but it sounds like a terrible idea, so don't do it. Why is the value of your primary key so important? Uniqueness is far more important, and reseting it undermines that.