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

> On Thu, Mar 23, 2006 at 11:04:55AM -0500, SGreen@stripped wrote:
> > saf@stripped (saf) wrote on 03/23/2006 10:50:10 AM:
> >
> > 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.
> 
> So I must do a big SELECT and then check my self every time (for each 
INSERT),
> which IDs are free?
> Hmm if the table has more than 100 000 entries, this will slow down my 
system.
> Specialitty because the check function would be written in PHP.

Lots of ways round this. Instead of deleting records, add a boolean 
"deleted" flag. All selects then need to add "and deleted = 0". But you 
can find a (random) deleted row with "select id from table where deleted = 
1 limit 1". If this returns a result, use update to re-populate that 
record, clearing the deleted flag. If it returns nothing, use insert to 
create a new record.

Alec

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