From my perspective there appears to be no eloquent theoretic underpinning to the behaviour of the auto_increment feature. For example, the exception to what freddy expains above is when the *last* record added to a table is deleted. The *next* record will have the same auto_increment value as the deleted record. As for foreign keys, hehehe - It would be funny if it wasn't so sad (that MySQL doesn't support them, yet). But you can see that because of this exception I describe you cannot rely on auto_increment to enforce the integrity of foreign keys.
Originally posted by freddydoesphp
Actually the ID 4 should not be reused, it wouldn't be a true auto-incrementing field if it did. What if you have some relations to this 4 in some other tables and when you delete an create a new record with ID 4 it will screw up your relations.
The 'auto_increment' is actually a very quick hack. It does as
Just before doing a 'write' to a table, the 'handler' code does the
- If there is a auto_increment field and the value of this is zero
- Set the table in 'read_only_keys' mode.
- Do a 'read_last' key on the table.
- Update the 'auto_increment' field with the found value+1.
- Set the table in 'normal' mode.
I didn't want to change the .ISM format when I added 'auto_increment'
and there is some issues that the above can handle much better than
with any other methods. For example:
- The above is very simple and one can always 'understand' what will happen.
There is no need to have special commands to reset a 'wrong'
- If one accidently inserts some wrong entries, one only have to remove the
entry and auto_increment continues as expected.
- One can start a new series by simple insert a new record at a new
The downside with this is:
- If one deletes the last record and inserts a new one will get the
last records id. As long as one first deletes all 'sub records' in
other tables this isn't really a problem.
- One can't start with a specific value without some simple hack.
Personally I think that the 'pro' is far better than the 'cons' :)