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