List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:June 14 2006 3:15am
Subject:Re: Reset (or Defrag) the AUTO_INCREMENT columns
View as plain text  
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
Thread
Reset (or Defrag) the AUTO_INCREMENT columnswolverine my14 Jun
  • Re: Reset (or Defrag) the AUTO_INCREMENT columnsDaniel Kasak14 Jun
    • Re: Reset (or Defrag) the AUTO_INCREMENT columnswolverine my14 Jun
      • Re: Reset (or Defrag) the AUTO_INCREMENT columnsDaniel Kasak14 Jun
        • RE: Reset (or Defrag) the AUTO_INCREMENT columnsDaevid Vincent14 Jun
  • Re: Reset (or Defrag) the AUTO_INCREMENT columnsMarco Simon14 Jun