On Aug 29, 2007, at 2:30 PM, Shawn Green wrote:
> 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
I see what you are getting at with this, and have decided that
mucking around with auto incrementing values doesn't exactly fit in
with the way databases were designed to work.
Somehow though, I still need to supply this whether I end up adding a
"Record" number field in the database, and then through php (The way
the database is going to be accessed) assigned a record number to
that field based on the total rows, and display that number rather
then the internal record number.
This is getting complicated :)
>
> --
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=japruim@stripped
>
>
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
japruim@stripped
