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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rtnylw
Forum Newbie
Posts: 4
Joined: Thu Sep 10, 2009 3:00 am

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

Post 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!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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.
(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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!
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

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

Post 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!
Post Reply