Bear in mind that the next increment +1 is NOT neccesarily the value you
will get when you add the next row.
If you begin a transaction, insert 10 rows and then roll back the
transaction, or delete the last 10 inserts, you loose those values.
That is to say
Id currently shows 1000
do 10 inserts
Id now shows 1010
Roll back the transaction, or delete the last 10 inserts
Maximum value in the ID column now shows at 1000, so you might expect the
next insert to have id value of 1001, but in fact it will be 1011, because
the autoincrement cannot take account of rollbacks and deletes. It cant for
User 1 inserts 10 rows
User 2 inserts 10 rows
ids = 1010 - 1019
User 1 rolls back his transaction.
User 2 inserts 1 more row. The database cant reuse 1000-1009 because user2
has already gone beyond that.
> Thanks for the info. I can look at the current value and add 1 to it for
> next increment. That works fine unless the table is empty, of course. I
> hoping for a way to work around this without the overhead of compacting.
> Guess not.
> > There is no way that I know of to get the next increment before it is
> > You can however, set the counter back to one on an empty table by
> > the database after the table is empty.
> > HTH
> > Del