A way to reorder laravel database columns after deleting

Question

I have a table where i can chose to delete a row. e.g i have table having 5 records numbered from 1-5, after deleting lets say 3, am looking a way that the remaining records will be 1,2,3,4 and not 1,2,4,5.

Answer 1

If you want to reorder by IDs, please think if it is necessary. Here is explanation https://laracasts.com/discuss/channels/eloquent/how-do-you-handle-with-reordering-items Also if you are using soft deletes it can cause a problem concerning unique IDs.

If you are using other column to reorder by, you can iterate through each entity and setting value to iterator value after deleting.

Answer 2

Warning: If you use this approach on Primary Key columns it might bring inconsistency and mess up your relations if you don't take care of it properly. Also most of the time it is unnecessary to reorder the primary key column.

The process can be applied from database itself. The general procedure is as following:

1. Make sure if the column is used as foreign keys in other tables, the definitions must have ON UPDATE CASCADE
2. (On production server) to reduce inconsistency put lock on the table.
3. Apply reordering

SQL:
For example you can run the following commands for MySQL (inspired by this answer)

-- if on production we lock the table write
LOCK TABLES my_reordering_table WRITE; 

SET @count = 0;
UPDATE `my_reordering_table` SET `my_reordering_table`.`id` = @count:= @count + 1;
ALTER TABLE `my_reordering_table` AUTO_INCREMENT = 1;

Warning: Again make sure you do not run this on production server and if you run, just make sure you have all the foreign keys ON UPDATE CASCADE.