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

[Ian Su]

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]

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]

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?
etc.

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.

[Bear Bibeault]

Please do not post the same question more than once.