File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes How to take up the deleted record id number by the successor record id number? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Reply locked New topic

How to take up the deleted record id number by the successor record id number?

Ian Su

Joined: Nov 18, 2010
Posts: 25
I would like to know something like this.I have a specific record to delete from a table.When the delete operation is successful,it will delete the row with specific id(Primary key & auto_increment').
Eg.I have a table with 3 rows and These 3 records have id field filled with int data(auto_increment).That is 1,2,3.What I would like to do is when I delete the 2nd row and the 3rd row id will automatically reassigned to the above row id(the deleted row id).The 3rd row id will be 2 and like that.How to do it?Is it possible to make it like that?I need some advice from you all.Thank you very much.
Mike Zal
Ranch Hand

Joined: May 04, 2011
Posts: 144

Why do you want to have this type of behavior? It is usually a bad idea to mess with auto-generated keys. You might be better off keeping the records in place and adding an boolean 'enabled' column or having a deleted record table. To accomplish your goal, you would probably need to use a procedural language like PL/SQL and would be an expensive operation. Is there some weird business constraint that forces you to do this?

chris webster

Joined: Mar 01, 2009
Posts: 2292

Mike's right: unless you have a reason to do otherwise, just let your auto-increment processing set your ID. This should ensure that nobody gets duplicate IDs, but of course the ID itself is a surrogate key i.e. just an arbitrary but unique number.

If you have to avoid gaps in your ID sequence, then implicitly your ID has meaning i.e. it is not just an arbitrary surrogate key, so you need to write your own code to maintain it, because you want it to follow your own rules instead of just being an arbitrary unique number.

You will need to think about a number of issues here:

How do you identify the next available value from the data table e.g. how do you write your code to fetch "3" if you already have IDs 1, 2, and 4?
How do you cope with the situation where two different users ask for a new ID at the same time?
How do you check for and handle duplicates in this situation e.g. do your users want to see error messages because somebody else used the next ID first?

This is a lot of work and can be quite fiddly to get right in a transaction-safe, thread-safe manner. So make it easy on yourself: use the auto-increment mechanism and don't worry about what the actual value is.

No more Blub for me, thank you, Vicar.
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 63866

Please do not post the same question more than once.

[Asking smart questions] [About Bear] [Books by Bear]
I agree. Here's the link:
subject: How to take up the deleted record id number by the successor record id number?
It's not a secret anymore!