I would like after each insertion or delete of my table to use a SQL script to rearrange the Ids.
Currently it looks something like this:
Id Nome
1 João
3 José
5 Ricardo
I want to leave it like this:
Id Nome
1 João
2 José
3 Ricardo
How to do it in the best way? (I do not have ID-linked keys)
There are 2 ways, but both are costly to use on a daily basis.
Firstly I will use your situation as an example, you deleted id 4 in this case it would look like this:
update tabela
set id = id - 1
where id > 4
Or, when it is a large amount and you do not know exactly which ones were deleted, the "right" would be to save your data a temporary or auxiliary table. Drop your table, and re-create it with the id of 0. But I do not know if it's workable. Or instead of dropping the entire table, simply delete the records and zero the auto increment of the id like this:
ALTER TABLE 'tabela' AUTO_INCREMENT=0
My SQL knowledge is not very advanced but I have learned a lot.
DECLARE @W INT = 1
DECLARE @ID INT = 1
DECLARE @Max INT = (SELECT MAX ([ID]) FROM [_Table])
WHILE @W Between 1 and @Max
BEGIN
IF EXISTS (SELECT [ID] FROM [_Table] WHERE [ID] = @W )
BEGIN
UPDATE [_Table] SET [ID] = @ID WHERE [ID] = @W
SET @ID +=1
SET @W +=1
END
ELSE
SET @W +=1
END