Delete and reallocate index

I'm finding it strange to have these two tables separate, but as there is no description of the case you might even need it. By the name and structure of the tables something tells me that it does not have to.

If you want an action in a table to trigger action in another table, you must use triggers . But if there is only one table or need it:)

The primary key should never be changed in the database. It does not matter that there will be holes. The only exception is if you create a routine that modifies all references to it throughout the database and can ensure that these keys were not used anywhere outside the DB. But I would not do it. virtually zero benefits and there are risks.

What you call index is this key, index is a set of keys.

To tell the truth in most scenarios I know nothing should be deleted effectively unless the person has a strategy for how to do this correctly.

Even though I wanted to do, I do not see the link between one table and another. If it is id then you do not really need two tables and then the question description is wrong.

To model right, create the right indexes, make the right queries, configure the server correctly, make the application the right way, and the architecture of the solution is adequate, make tests, analyze the usage, see the bottlenecks, all this will have performance.

Responding to your comment on Maniero's response:

Suppose you have multiple records:

n
1
2
3
4
5
6

You will delete the n 3. After deletion, you would simply issue a command:

update tabela set n = n-1 where n > 3;

But also, as Maniero said, I see nothing that solves it, and something totally unnecessary.

If you want a select, the number of the row to come in sequentially without jumps, there are functions that do this.