Reorder ID in table after Delete or Insert

Asked

Viewed 153 times

0

I have a table with a column called ID. That one ID is neither Auto Increment nor primary key, but follows a numerical sequence (e.g.: 1, 2, 3, 4, 5...).

I need the include or to the delete in a variable position, recalculate the Ids and do the update. Example:

  • if DELETE ID 2, the next Ids are subtracted 1. Where the 3 had seen the 2, 4 flipped 3, etc..

The same thing when INCLUDE in a variable position. Any idea how to do?

php

  • Could you translate the question into English?

  • Mike, welcome to [en.so]! Translate the question to Portuguese it will be reopened. Hug.

  • Which set of data?

  • 1

    What is this for? It will include unnecessary complexity in your application. Think about the cost of moving, say, 10 million records several times a day because of this.

  • @Ike you could justify this behavior so that we can help you ?

  • This table is a foreign key in some other table !? Reordering would lose the link. Is there any strong reason not to have "toothless" in this table ? Outside.

Show 1 more comment

2 answers

1

When something gets crooked late or never straight. I would do so: - reorganize the table, leave the right Ids. It is not difficult, but it can take or spend a lot of resources

select id from table
$n=1;
while($row=fetch)
{
   UPDATE table SET id=$n WHERE id = $row[id];
   $n++;
}
  • instead of delete, when to take some record, I would update the row, leave all columns blank, except the ID that gets the original value.
  • when it was necessary to enter a value, look for the lowest available ID. If there was no available create a new line.

Then it is easier to maintain. If you start having too many empty lines, fill these lines with the last entries and delete the last entries.

0

Based on the answer above (or below) that will possibly be deleted or incorporated into the initial topic in which you say you intend to recreate this database one day, I believe the reason is more cosmetic.

So, as I also believe that you should not have as large a fragmentation as you seem to have today in short intervals of time so, to reorder everything at once, simply delete the primary key column and recreate it.

But it is extremely important that, from now on, you get used to the fact that your Ids will not always be sequential.

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.