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?
[EMAIL PROTECTED]
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
