I was wondering what the simplest way of letting a table set ids automatically is, apart from AUTO_INCREMENT.
I have a table that will accumulate thousands of rows over a long period of time. Some of these rows will be deleted at a later time. How do I get new rows to get the lowest available id, without getting into doing it manually.
Can I for instance reset AUTO_INCREMENT every time someone adds a row?
Frankly it isn't worth the hassle. I believe its possible, but it's just a unique identifier at the end of the day - doesn't matter if its 1 or 1000.
If it's for other purposes, consider using another field.
IMO you shouldn't mess with the indexes.
Not possible, an ID is unique and can be used once..
only possible if you Empty your table.
Who cares? An integer can have billions of unique values, all of them cost the same amount of memory (4 bytes each). Gambling with the primary key is like russian roulette to your database: In the end your database will die (it gets corrupted).
I guess you could use triggers for this, but this will probably turn into a huge mess after a while. Don't see why you wouldn't want to use auto_increment instead, do you have any particular reason?
Triggers in MySQL 5.0 - http://dev.mysql.com/doc/refman/5.0/en/triggers.html
@Jordy: Your statement is not all true, yes only one row can have that one value at any given point, but if you delete a row you can still reuse the deleted value for another row. You can also reset the counter to any value you want to including the increment without deleting the table.
It is possible to reset the auto increment to the next possible ID:
ALTER TABLE table_name AUTO_INCREMENT = 1
You don't even have to set it to the next available key. MySQL will automatically take the next key on the next update. So even setting it to "1" every time is safe.
But if you don't care about the ID why do you even use a ID? And also even large numbers of IDs are not a problem. I got a table with more than 20 million entries and never had a problem with the IDs.