List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 23 2006 4:28pm
Subject:Re: Question about autoincrement ID
View as plain text  
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




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