SQL: Auto Increment question

Question

I have a table with an auto_increment field and sometimes rows get deleted so auto_increment leaves gaps. Is there any way to avoid this or at the very least write an sql query that a) alters the auto_increment value to be the max(current value) + 1 and b) return the new auto_increment value?

I know how to write part a and b but can I put them in the same query?

If that is not possible or additionally: how do I "select" (return) the auto_increment value or auto_increment value + 1?

Answer 1

not sure if this will help, but in sql server you can reseed the identity fields. It seems there's an ALTER TABLE statement in mySql to acheive this. Eg to set the id to continue at 59446.

ALTER TABLE table_name AUTO_INCREMENT = 59446;

I'm thinking you should be able to combine a query to get the largest value of auto_increment field, and then use the alter table to update as needed.

Answer 2

Renumbering will cause confusion. Existing reports will refer to record 99, and yet if the system renumbers it may renumber that record to 98, now all reports (and populated UIs) are wrong. Once you allocate a unique ID it's got to stay fixed.

Using ID fields for anything other than simple unique numbering is going to be problematic. Having a requirement for "no gaps" is simply inconsistent with the requirement to be able to delte. Perhaps you could mark records as deleted rather than delete them. Then there are truly no gaps. Say you are producing numbered invoices: you would have a zero value cancelled invoice with a that number rather than delete it.