wolverine my wrote:
> May I know if there is any way we can reset (or defrag?) the values
You shouldn't do that. It's much easier for you as a database
administrator if these values are left up to MySQL.
What happens, for example, if you restore from a backup that has rows
which you recently deleted? You'll have a number of records ( possibly
many ), all with the same primary key. How do you figure out which
record is the oldest one, which is the 2nd oldest one, which is the
current one, etc? What happens if you have records in a related column
that were referring to this primary key? You've got a big mess!
Also, there's no such thing as 'defrag'ing an auto_increment column. The
space left when you delete a row will be taken by another record when
MySQL sees fit.
If you absolutely must have a continuous stream of numbers for your
primary key, then don't use an auto_increment column. Instead, use a
numeric column and write some code to create your next primary key value.
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989