Как произвести сортировку в таблице SQLite3, после удаления записи?

Question

[andishub]

как произвести сортировку в SQLite, если пользователь решил удалить любую запись.
Например: Есть таблица А:

id| name
--| --
1 | John
2 | Mary
3 | Peter
4 | Paul
5 | David

И в этой таблице решили удалить запись с id 3, то получится так:

id| name
--| --
1 | John
2 | Mary
4 | Paul
5 | David

Как можно изменить порядок id так, чтобы Paul стал с id 3, а David с id 4?

Знаю про этот способ:

UPDATE table_name
SET id = id + 1
WHERE id > 3;

Но его костыль в том, что он может работать с условием того, что пользователь удалить запись конкретно с id 3. Есть ли другие решения, которые могут сработать при условии удаления любой записи с любым id?

Comments

[o5a]

В комментариях уже ответили верно, что первичный ключ сделан не для того, чтобы идти по порядку без пробелов. Его задача - поддержка уникальности. Но хотелось бы узнать, а для чего вообще захотели пересортировывать эти номера?
Если была цель - выводить их как номера строк, то для этого в SQL есть отдельные функции, которые нумеруют строки по порядку (без пробелов), причем нумерация может быть разная в зависимости от поля сортировки.
https://www.sqlitetutorial.net/sqlite-window-funct...
Т.е. в вашем случае может быть так

select id, name, 
row_number() over(order by id) rn_1, -- порядковый номер при сортировке по id
row_number() over(order by name) rn_2, -- но можно и при сортировке по именам
from table_name

Так что не нужно пытаться поддерживать порядок внутри самой таблицы, его получите при запросе данных из нее.

[andishub]

o5a, добрый день! у меня была цель, чтобы при выводе записи из бд, была нумерация. те. вот примерно так:

запись 1
запись 2
запись 3

ну, конечно же после удаления все рушилось (нумерация), но я немного иначе сделал. использовал цикл и i+=. вот так вот и сделал то, что я хотел

[o5a]

andishub, значит я правильно понял, для чего это потребовалось. Приведенный row_number как раз и решает вашу проблему. Она как раз и используется в SQL для вывода порядковой нумерации данных. Лучше делать через нее, не стоит городить "i+="

Answer 1

[Vindicar]

Ты просто не понимаешь сути первичного ключа.
Это значение, которое однозначно идентифицирует строку, независимо от наличия и количества других строк в таблице. И точка. Он не должен меняться вообще за время жизни строки.
Бывают случаи, когда у нас уже есть уникальное значение, которое можно использовать как ключ.
Но куда чаще используются синтетические ключи, т.е. просто числа, которые пользователь по-хорошему и видеть-то не должен никогда. А раз так, то какая разница, есть ли дырки в нумерации?
Я больше скажу, иногда сплошная нумерация - это слабое место в системе.

Answer 2

[mayton2019]

Все базы данных хранят строки не так как Excel. Хранят как предписано в файловом
формате для целевой DBMS. Когда удаляешь - обычно
остаются дырки которые потом заполняются другими строками. Но это я упрощенно рассказываю.
В реальности все еще сложнее. И есть процессы вакуума или другой релокации строк. И таблица
может двигаться в памяти. Шаффлиться. Делиться на части. Кластеризоваться и т.п.

Поэтому твоя хотелка достигается только с использованием order by.

Все остальные способы не работают потому что язык SQL не дает никаких гарантий относительно
физического упорядочивания строк.

UPD: Для авто-нумерации https://stackoverflow.com/questions/16847574/how-t...