[EMAIL PROTECTED] (saf) wrote on 23/03/2006 16:10:04: > 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. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
