Yes, I agree on what you have described.
However, what should we do when the value is reaching the maximum? To
alter the data type to a bigger one?
On 6/14/06, Daniel Kasak <dkasak@stripped> wrote:
> 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.
> Daniel Kasak
> IT Developer
> NUS Consulting Group
> Level 5, 77 Pacific Highway
> North Sydney, NSW, Australia 2060
> T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
> email: dkasak@stripped
> website: http://www.nusconsulting.com.au