In my mysql database users I have a table with 3 fields, ID, fName, lName. The ID field is what everything is sorted by and its auto_increment. Now lets say I fill up this table with these values:
0 Kris Ennay
0 Bob Barker
0 Tommy Hildude
The ids would be 1, 2, and 3. Now lets say I want to delete Tommy from the table so I get rid of his row. Now the data in table is:
0 Kris Ennay
0 Bob Barker
I then add a row, and that rows id turns out to be id 4?? Is there I way I can get the previous rows ID then increment it one and make that the new rows ID?
Thanks for all help and advice provided.
Help with php and mysql
Moderator: General Moderators
mysql (probably like all databases do) maintains a (more or less) hidden field for each table that contains an autoincrement-col. The number stored in this field is used to determine the next auto-id. But neither does mysql check for holes nor wether a DELETE took the highest current id.
The purpose of such a counter and an autoincrement-col is to provide a unique id for each row in a table regardless of other actions (like delete), do not play tricks on this
btw: Not a client side question
The purpose of such a counter and an autoincrement-col is to provide a unique id for each row in a table regardless of other actions (like delete), do not play tricks on this
btw: Not a client side question