Recycling Deleted ID

joon

data in myTable
(ID) say

(1) hello
(2) hello
(4) hello
(6) hello
(7) hello

I have the myTable like the above.
ID is the primary key.
RecordCOLOR=Red[/COLOR] and record COLOR=Red[/COLOR] were deleted, so they are omitted.

Now let's excute the following code 3 times.

insert into myTable ( say )
values ('hello')

If the primary key is auto_incremental, the new records will be generated like the following.

(8) hello
(9) hello
(10) hello

I like the generate new records like the following.

COLOR=Red[/COLOR] hello
COLOR=Red[/COLOR] hello
(8) hello

Can I do it with your help?
( You may think that recycling the deleted ID is a terribly bad idea, but I have my own reason for it...)

Thanks in Advance

devinemke

if you run the following query:

ALTER TABLE tablename AUTO_INCREMENT = 1

you can reset the auto_increment for the last deleted id. this still won't help you for older deleted records (where newer records have been added). for that you would have to loop through the table and find the next missing id (which seems to me like more truble than it's worth).

joon

Hm, can I find the first omitted ID?

It would be something like the following code.

select ID
from myTable
where ID = [color="red"]the first omitted ID[/color]

target result
color="red"[/color]

Bunkermaster

store keys to recycle in a seperate table, don't touch the auto_increment, or just make a field with a boolean saying used/not used and use the first one you find free (do not omit to change value when you clear the record and to ignore those when displaying database content) then it's just a matter of
SELECT
id
FROM
foo
WHERE
bar = 0
ORDER BY
id
LIMIT 1

devinemke

<?php
$result = mysql_query('SELECT id FROM table ORDER BY id');
$i = 1;
while ($row = mysql_fetch_assoc($result))
{
	if ($row['id'] != $i)
	{
		echo 'first open id slot at ' . $i;
		break;
	}
	else
	{
		$i++;
	}
}
?>

Bunkermaster

Originally posted by devinemke

<?php
$result = mysql_query('SELECT id FROM table ORDER BY id');
$i = 1;
while ($row = mysql_fetch_assoc($result))
{
	if ($row['id'] != $i)
	{
		echo 'first open id slot at ' . $i;
		break;
	}
	else
	{
		$i++;
	}
}
?>

[/B]

No offense dude but eww!!
this will work but it also will potentially have your script cycle gazillion times before finding an empty slot and thus slow down the page and potentially kill the server if 1000 people do an addition at the same time.

devinemke

Originally posted by Bunkermaster
No offense dude but eww!!
this will work but it also will potentially have your script cycle gazillion times before finding an empty slot and thus slow down the page and potentially kill the server if 1000 people do an addition at the same time.

which is precisely why i said in my first post "which seems to me like more trouble than it's worth"

joon

Originally posted by Bunkermaster
store keys to recycle in a seperate table, don't touch the auto_increment, or just make a field with a boolean saying used/not used and use the first one you find free (do not omit to change value when you clear the record and to ignore those when displaying database content) then it's just a matter of
SELECT
id
FROM
foo
WHERE
bar = 0
ORDER BY
id
LIMIT 1

It is a great idea indeed for the terribly bad idea of mine!

tomhath

No, it's not a good idea at all. In fact all of the suggestions will cause you big problems because of what is called a "race condition". If you don't know that it is or how to avoid it, use autoincrement.

For example, suppose two requests come in a few milliseconds apart:

1) The first looks up a key to recycle. While the database is looking up the value, the second request also tries to look up a key but it is blocked, it has to wait until the first query finishes to access the database.

2) The first request gets the key from the database, the second is now unblocked and looks up the next available key from the database.

3) The first request tries to update the database (to make the key it has unavailable) but it's blocked waiting for the second request.

4) The second request returns (with the same key as the first). You now have a big problem in your database.

This is almost guaranteed to happen if you have any amount of traffic making requests because disk and database I/O is so slow compared to the CPU, processes tend to get queued up waiting for the database.

Bunkermaster

my select can be turned into an update (1 query instead of 2) without much effort and to be honest recycling keys is always a bad idea...
But still you're right. Many other things can happen:
- no key to recycle (amazingly enough at start it will happen)
- one key to recycle and 2 new records working on the assumption there is one free (and trying to update etc.)

Voila.
I personally NEVER do that for the simple reason that one day, I know the database structure is liable to change and I will have to enforce referential integrity :p

Sxooter

The main reason this is such a bad idea is the possibility that a subordinate record that was once associated with a now deleted ID will be associated with a new master record that it shouldn't be. Here's a hypothetical:

master table:
ID | name
1 | john smith
2 | joan smith

allergies
ID | allergy
1 | NONE
2 | penicillian

Now, the table gets updated, John Smith is removed from the system, and a new person is added, but the old 1|NONE entry gets left behind:

master table:
ID | name
1 | joe smith
2 | joan smith

allergies
ID | allergy
1 | NONE
2 | penicillian
1 | tylenol

And the computer winds up going with just the first record in allergies, NONE, joe smith gets tylenol, and dies.

This kind of stuff can and does happen when you reuse master keys. Usually it's less life threatening, of course, like someone getting a Best of Sonny and Cher CD along with their order for Nellie Mckay, but who knows, that could prove fatal for a certain small percentage of the population as well :-)

Bunkermaster

sometimes i miss Oracle and I am impatient for relationships and triggers (and then I remember I hate working for corporations)

Sxooter

PostgreSQL and Firebird have both of those things ya know. Conversely, there are small corporations that use Oracle. I'm going to work for one starting next week. They use both Oracle and PostgreSQL.

:-)

Bunkermaster

Congrats 😃
We run some oracle but I don't get to play with it :p I have my gazillion mysql DBs it's all cool for me 😃

BSTRhino

Here's my idea.

SELECT TOP 1 id
FROM myTable t1
WHERE NOT EXISTS (SELECT * FROM myTable t2 WHERE t2.id = (t1.id + 1))
ORDER BY t1.id ASC

This will give you the id just before the first empty slot. So, if it returns 2, there's an empty slot at 3. If MySQL supports the NOT EXISTS command, then it should work fine.

But still, like everyone has said here before, it means that the ID field no longer uniquely identifies something, so it's kind of dangerous.