Filling auto_increment fields

[misterx]

I wanted to have each row in my table get an automatically incremented id number so I added an auto_increment column. There were originally 4 rows and it numbered them nicely from 1 to 4.

Then while testing I deleted the row with id=4 and added a new row, using NULL as the id value.

I would like it to give the new row the id of 4 since it is technically the 4th row but it gave it 5, as in the 5th one ever created.

Just wondering if there is a way around that. Thanks.

[WA]

MySQL actually keeps track of deleted fields as well, and the ID for the deleted field is still "retained" even after it's deleted. This means if you delete a row with say ID=4 and add a new row, the new one will automatically receive ID=5.

I don't believe there's a way to overwrite this behavior of auto incrementing fields in mySQL. It has to do with generating a truly unique ID number for each row you add.

[misterx]

Ok, well now at least I know why it does what it does. It's not really impacting so I'm going to leave it the way it is. Thanks for the info though.:)

[oracleguy]

The only way I know of to reset the auto-increment number for a table is to run a:

DELETE * FROM <table>

then it seems to reset... of course this isn't very practical but just FYI.