I have a column in a table containing the row_number, some kind of id number to identify the row. When I delete one row, I need the other rows to have their row_number decremented, to keep them sorted. What's the best way to do it? I've seen that auto_increment is not good, so what do can I use? Programatically updating all tables entries sounds bad for me, isn't it?

| improve this question | | | | |

If the objective is to be able to sort the rows according to row_number then don't bother updating values on a delete. The rows will come out in the same order after a row is deleted as they would before. It is not worth the effort to close up the gaps. In the worst case, if you delete the lowest-numbered row, you will have to update every remaining row in the table, in a transaction, with locking and all the concurrency problems that involves. Don't do it.

If you absolutely must have some visual indication to users then let the application UI produce one, or use a function like ROW_NUMBER() at run time (SQL Server; other RDBMSs have similar).

| improve this answer | | | | |
  • i need the row info to be accessed when the user clicks on a button (each row will have a specific button). i think row_number() will do. thanks – tinyeyes Dec 31 '14 at 5:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.