Hi Jason,
Jason Pruim wrote:
> Is there away to reset an auto incrementing field count? I have a
> database that currently has 935 records in it but because I have deleted
> a few the current number used for NEW records is 938 :) How can I get it
> to count the records and assign a record number based on the total count?
>
> Hope that makes sense! Thanks for looking! :)
>
Actually, it doesn't make sense and for the very reason you are trying
to use it. At some point in history you had a record # 936. Because that
record once existed, there may have been one or several things
associated with it. Imagine the confusion that would ensue if the Social
Security administration recycled an already issued number just as soon
as the person using it died.
The safest thing to do is to pretend that the auto-incrementing field is
an internal, non-editable field. Should you have gaps in your auto-inc
values treat them as normal conditions of having an active database.
For another instance, assume that you are auto-incrementing the serial
numbers to various items in an inventory control system. If an item is
destroyed or taken out of use, you probably want to move that record
from an "activeitems" table to some other location. Would you want to
re-issue those numbers to newly purchased items just to fill in the gaps
in the "activeitems" table? Of course not.
Now, with the understanding that doing this on a regular basis would be
wrong, here is how to do it anyway: Use the "auto_increment=" option to
an ALTER TABLE statement like this
ALTER TABLE mydata AUTO_INCREMENT=936;
(alter table)
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
(for the definition of "table option")
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html
