[EMAIL PROTECTED] (saf) wrote on 03/23/2006 11:10:04 AM: > On Thu, Mar 23, 2006 at 11:04:55AM -0500, [EMAIL PROTECTED] wrote: > > [EMAIL PROTECTED] (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
