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
