Auto Incrementing MySQL fields

[holmescreek]

Quick question. I have a mysql database and the first field, the primary key "ID" is set to autoincrement. I noticed that, for example, if you delete the record with the ID of 4, then add a new record, shouldn't MySql use the ID of 4? In old Dbase the pack command did this. Suggestions?


thanks in advance!

[Justin Sampson]

Yup it should. I now that with older versions of mySQL (sorry, I can't remember the version's) it didn't but it does automaticly now.

[holmescreek]

hmm, weird. Maybe it just does it on some type of random basis rather than after inserting the new record right after the delete.

[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. Furthermore an auto-incrementing number should ONLY be used to associate itself with a record and never for counting records. That is why it doesn't fallback to an allready used id number.

[holmescreek]

So, in theory every time you delete a record the maximum possible records your database can hold will decrease by a factor of 1?

[freddydoesphp]

I suppose in theory yes, but if you are using int(11) that is a whole hell of a lot of records.

[firepages]

But you can "OPTIMIZE TABLE $tablename" - which will let mysql reclaim space used for deleted records.

[freakysid]

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.

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.

I found this post made by Fred Lindberg (Monty)to the MySQL mailing list
http://www.tryc.on.ca/archives/mysql/Jun1997/0201.html

The 'auto_increment' is actually a very quick hack. It does as
follows:

Just before doing a 'write' to a table, the 'handler' code does the
following:

- 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'
auto_increment value.
- 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
bigger range.

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'

Monty

[MattR]

All I can say is:
*sigh*

(that is my "Sigh of profound hatred" -- something I usually reserve for horrible bugs in software...)

[holmescreek]

*sigh* you better fix the photo of Beaker, you sinfull person you!