How can we re-use the deleted id from any MySQL-DB table?
If I want to rollback the deleted ID , can we do it anyhow?
+3
A:
It may be possible by finding the lowest unused ID and forcing it, but it's terribly bad practice, mainly because of referential integrity: It could be, for example, that relationships from other tables point to a deleted record, which would not be recognizable as "deleted" any more if IDs were reused.
Bottom line: Don't do it. It's a really bad idea.
Related reading: Using auto_increment in the mySQL manual
Re your update: Even if you have a legitimate reason to do this, I don't think there is an automatic way to re-use values in an auto_increment field. If at all, you would have to find the lowest unused value (maybe using a stored procedure or an external script) and force that as the ID (if that's even possible.).
Pekka
2010-04-13 12:18:00
@Pekka I have scenario like If I am testing my data by deleting the previous one but it didnt work for me, but now I have to reuse the old data with the same old ID then what?
OM The Eternity
2010-04-13 12:21:41
@Parth I don't understand your situation, but if you have to reuse old IDs you most likely have a design flaw somewhere. Can you elaborate in your question?
Pekka
2010-04-13 12:22:38
@Pekka, I am tracking the INsert Update and delete queries in a table, I am doing good with insert and Update as i dont face any problem with ID there, But once the delete action is performed I lose the unique ID which might be used at many places, and reinserting the old data will give new ID not the same old ID, hence for this case I need Old ID so that I can replicate the query in other db with same old ID
OM The Eternity
2010-04-13 12:27:34
Just watch his question history. I's the comprehensive explanation.
Col. Shrapnel
2010-04-13 12:31:53
@Pekka Did u got my point?
OM The Eternity
2010-04-13 12:36:56
@Parth not entirely to be honest - if you delete a record, why are there relationships to the record still in existence? Anyway, as I said, it's really tough to do in mySQL alone. You'd have to cook something up in the scripting language of your choice I think.
Pekka
2010-04-13 12:39:43
+3
A:
You shouldn't do it.
Don't think of it as a number at all.
It is not a number. It's unique identifier. Think of this word - unique. No record should be identified with the same id.
Col. Shrapnel
2010-04-13 12:20:14
Agreed. This is nothing like a fragmented drive where things are less efficient if they're spread out. As long as your id's datatype can handle the values, don't worry about it. If it can't handle the values, get a new datatype.
CaseySoftware
2010-04-13 12:48:49
@Parth If your ID's are currently UNSIGNED INTs, and you're approaching 4,294,967,295 records, upgrade to UNSIGNED BIGINTs (until you have 18,446,744,073,709,551,615 records).
Dolph
2010-04-13 15:04:21
@Parth SERIAL becomes a useful alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
Dolph
2010-04-13 15:11:00
+1
A:
1.
As per your explanation provided "@Pekka, I am tracking the INsert Update and delete queri..." I assume you just some how want to put your old data back to the same ID.
In that case you may consider using a delete-flag field in your table. If the delete-flag is set for some row, you shall consider program to consider it deleted. Further you may make it available by setting the delete-flat(false).
Similar way is to move whole row to some temporary table and you can bring it back when required with the same data and ID. Prev. idea is better though.
2.
If this is not what you meant by your explanation; and you want to delete and still use all the values of ID(auto-generated); i have a few ideas you may implement: - Create a table (IDSTORE) for storing Deleted IDs. - Create a trigger activated on row delete which will note the ID and store it to the table. - While inserting take minimum ID from IDSTORE and insert it with that value. If IDSTORE is empty you can pass NULL ID to generate Auto Incremented number.
Ofcourse if you have references / relations (FK) implemented, you manually have to look after it, as your requirement is so.
Further Read: http://www.databasejournal.com/features/mysql/article.php/10897_2201621_3/Deleting-Duplicate-Rows-in-a-MySQL-Database.htm
kv
2010-04-13 14:02:09