Re: Reset (or Defrag) the AUTO_INCREMENT columns

[wolverine my]

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: ([phone] / F: ([phone]
> email: dkasak@stripped
> website: http://www.nusconsulting.com.au
>