Possible is, but it is not as easy as in a stack if you imagine a stack physically in an environment where there is gravity, if you take any element from it, logically will "fall" and occupy new positions. A table is not a stack ...
Answering your question:
The easiest way to do this would be to delete the ID column and re-create it.
alter table X drop column ID; /*Exclui-se a coluna*/
alter table X add column ID integer not null auto_increment primary key;
You only have a problem doing this, if your table has FK ( Foreign Keys ) you will not be able to delete the column without losing the references in the other tables.
If you need to keep the reference, I recommend using a Stored Procedure , for example, once a week, or a month ...
The Stored Procedure would need to have a cursor and a counter . The cursor would be for you to scroll through the table (it is nothing more than a SELECT) and the counter will contain the value of the expected ID, which is nothing more than the value of the ID that is expected (ie, the previous record ID + 1). Within the cursor loop, you check (IF) if the ID is equal to the value of the counter, if it is beauty, you add 1 to the counter and follow the loop, if not you do an update on that line, Its ID is now the value of the counter and it follows the process (add one to the counter and follow the loop)
Just remembering that this is a very high cost for the database (cost => Processing), I would like to know why you want to do this ...
Some points about this reordering:
-
ID is short for IDentity or IDentification , it does not make sense for you to change this field. Do you imagine that every time a person dies, the Government wants to update the CPF and / or the RG of all the others who are still alive? Have you thought about chaos?
- Imagine that you have a table with 10k (one thousand) records. You delete the 1, doing the last way I said, you would have 9999 UPDATES, because everyone would be wrong (you update 2 for 1, 3 for 2 ... the 10000 for 9999), you can be sure that you have a mega LOCK in your database and many I / O problems.
- If you use the ID for some external reference (whether to generate links on your site, access to report by another system, whatever), by doing so, such as that you upgrade to the other location that is saved, that you updated on your table so that the reference is not lost?
- You will use a lot of processor and memory of your server just to leave the records "organized".
- I recommend that you do not delete / remove the records from the database, but flag them as inactive, so you have them saved in your database, but your system knows it is not to use, so you can restore some registry easily, and you still do not have this "organization" problem.
