Page 1 of 1

AUTO INCREMENT: re-use empty ID's, possible?

Posted: Thu Sep 10, 2009 3:21 am
by rtnylw
Hi,
Lets say I have 10 records in a table, the table has one field (ID INT PRIMARY KEY AUTO INCREMENT). When I delete record #9 and add a new record, the new records ID is going to be 11. Is it possible to set up a table in a way, so sql checks if there are any records missing and re-uses the ID? so that when I add a new record, it would go into #9, since it's empty. Hope that makes sense. Thanks!

Re: AUTO INCREMENT: re-use empty ID's, possible?

Posted: Thu Sep 10, 2009 4:00 pm
by Christopher
I think maybe you misunderstand databases. There is nothing 'empty' when the record with key value 9 is deleted. Autoincrement is just a way to guarantee unique values for keys. There is little value in using deleted values. If you must, then add a 'active' column and instead of deleting, set that record to inactive. Then when adding, search for inactive records and update the first available. That is a lot of work for nothing in my opinion.

Re: AUTO INCREMENT: re-use empty ID's, possible?

Posted: Thu Sep 10, 2009 9:07 pm
by califdon
arborint wrote:I think maybe you misunderstand databases. There is nothing 'empty' when the record with key value 9 is deleted. Autoincrement is just a way to guarantee unique values for keys. There is little value in using deleted values. If you must, then add a 'active' column and instead of deleting, set that record to inactive. Then when adding, search for inactive records and update the first available. That is a lot of work for nothing in my opinion.
Absolutely, positively true, for all circumstances and all applications. If you have some other situation that (for what reason, I can't imagine) requires you to have consecutive numbers, do not use Autoincrement!

Re: AUTO INCREMENT: re-use empty ID's, possible?

Posted: Fri Sep 11, 2009 5:21 am
by Darhazer
And, in that theoretical situation, it be lot easier to update all ids after the deleted one to set them -1 when you are deleting, then to insert the record in the place of the old one. And in this way there won't be a 'hole' between the delete and the new insert. But if we are talking about the primary key, and not just some column that need sequential numbers... do not do that!