List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 23 2006 4:04pm
Subject:Re: Question about autoincrement ID
View as plain text  
saf@stripped (saf) wrote on 03/23/2006 10:50:10 AM:

> Hi,
> 
> I have a question about autoincremend id:
> If I have an autoincrement id set on my first column field of my 
> table and I have the
> following entries:
> 1
> 3
> 
> And then I make a INSERT INTO foobar VALUES(''); , the next field 
> would be automatically 4:
> 1
> 3
> 4
> 
> Is there a possibility to take a free ID to not use too high IDs for 
nothing?
> I would like to take the ID 2 and not 4, because ID 2 is free.
> 
> My problem is that my system which uses the ID numbers in 
> applications which uses them
> as signed int or unsigned int,
> so I will soon have a problem, because I insert (and delete some 
> times) many entries in my SQL database,
> but not more than the highest value of an signed integer.
> 
> 
> -- 
> Best regards,
> saf
> http://TrashMail.net/


The short answer is "no".  The Record #2 already existed. It's current 
status is "deleted". If you had other tables that linked their data to 
record #2 and you created a new #2 to replace the one you already deleted 
then you could possibly be making a "bad" match between the old data and 
the new data. 

For the sake of data consistency and for all of the other good reasons to 
have a relational database, once an auto_increment value has been issued 
it's considered used and no other record should ever have that number. 
Only if you completely reset your table (see the command "TRUNCATE TABLE") 
could it be possibly safe to begin re-issuing the smaller numbers. Again, 
it's only possible if all of the child records that used to point to the 
old data were also deleted.

Do not rely on the auto_increment value for record sequencing. If you need 
your records serialized in some sequential way, you will need to code the 
support for those sequential numbers in your application.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thread
Question about autoincrement ID(saf)23 Mar
  • Re: Question about autoincrement IDSGreen23 Mar
    • Re: Question about autoincrement ID(saf)23 Mar
      • Re: Question about autoincrement IDAlec.Cawley23 Mar
      • Re: Question about autoincrement IDSGreen23 Mar
        • Re: Question about autoincrement IDmysql23 Mar
  • Re: Question about autoincrement IDMartijn Tonies23 Mar
Re: Question about autoincrement ID(saf)23 Mar