List:General Discussion« Previous MessageNext Message »
From:Fred Read Date:April 30 1999 8:17am
Subject:Re: Auto-Numbering
View as plain text  
Dave Crawford wrote:
> In regards to a auto_increment 'ID' column, is there a way 
> for the Mysql server to assign the LOWEST available 'record' 
> number (if one is available) instead of using the highest 
> existing record + 1? 
> For example if I have 20 records and I delete row 11, when 
> I insert the next record assign it number 11 instead of 21. 
> Is there an easier way to accomplish this other than polling 
> a range of record numbers and seeing which rows come back NULL? 

I would do this by adding a "Deleted" column to your table.
Instead of physically deleting rows you set the Deleted flag;

    "update table set Deleted = 'Y' where ID = x".

When you wanted to insert a new record you first find the 
lowest ID value; 

    "select min(ID) from table where Deleted = 'Y'

You then "insert" your new row;

    "update table col1 = NewValue, col2 = NewValue ...
     where ID = <value returned from select>"

This works fine for a single user application, but if there 
is a possibility of concurrent updates you first read the 
row and modify the where clause to include the old values;

    "update table col1 = NewValue, col2 = NewValue ...
     where ID = <value returned from select>, 
     col1 = OldValue, Col2 = OldValue ..."

Failure to do this can result in data loss!

Hope this helps...

If it ain't opinionated, it ain't Rich Teer.
Auto-NumberingDave Crawford30 Apr
  • Re: Auto-NumberingFred Read30 Apr
  • Re: Auto-NumberingChristian Mack30 Apr