MySQL Auto Increment Question...

[Superwormy]

Ok, So I have a MySQL table, interfacing with it with PHP.

Now, the first column of the MySQL table is set to AUTO INCREMENT.

So, say I have 3 records inserted in the table, ids: 1, 2, and 3
Now, I delete record number 3, and then I insert more records. Well, those new records get inserted as records 4, 5, 6 etc... BUT even though I deleted records number 3, it'll skip numbering anythign record 3 for some reason...

And I don't want it to do that. How can I stop it from doing that???

thanx in advance.

 

[RedRooster]

I don't think there's a setting in mysql that'll let you re-use ids, as it's there for database integrity reasons. For example, say you delete that id3 record, yet you have other tables that rely on that id as a key. If you delete it without deleting the associated records from other tables(which can happen, either by poor coding or other reasons out of a programmers control) and then add a record with an id of 3, things could get ugly.
The only thing I could suggest, if you REALLY NEED to re-use ids, is to use the LAST_INSERT_ID() mysql function and do some fancy subtracting and querying to check if any numbers before it are used. A real pain in the arse though.

Or, don't make it an auto_ increment field, and just hard code the id tag on every insert.
Or, I'm not sure if this would work, but you could do an alter table and set the auto_increment to 1 after every insert, but I'm not sure if it'll start at one and find a non-used id, or if it'd just start going up incrementaly and overwriting anything there. I wouldn't try that one though.

I've been doing it for 5 years(I wouldn't suggest ever doing any type of coding for that long ) and it's not something I would ever try to do. Even with space concerns.

 

[Argo]

Only way to achieve what you want is programmatically. And even then it's pretty ugly and slow.