saf@stripped (saf) wrote on 03/23/2006 11:10:04 AM:
> 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.
>
> --
> Best regards,
> saf
> http://www.trashmail.net/
>
No, you should quit trying to tell the database how it should implement
auto_increment. If you don't want a monotonically increasing integer value
to be automatically generated for each new record (or attempted new
record) then simply don't use auto_increment. At that point you can make
your ID values anything you want because you are going to be completely in
charge of creating them.
There are dozens of great reasons why the database has an auto_increment
function built into it. There are probably as many reasons why doing what
you propose to do is normally considered "very bad practice". What's the
real reason you don't want to let auto_increment do its automatic
numbering?
Many of us on the list manage databases with millions or billions of rows
in our tables and we DO NOT even attempt to "fill in the gaps" as you
propose to do. There is just no good reason to do it, and several good
reasons to NOT do it.
One important thing to remember: You should not let UI design requirements
dictate your DB design. Most developers who design the database just to
support the front end up regretting the decision. Those designs are either
impossible to extend or impossible to manage or both. You should always
design for an efficient database and adjust your retrieval methods to
present the data in the manner requested, not the other way around.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
