Атомное многострочное обновление с уникальным ограничением

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

create table label (
  id_label serial not null,
  rank integer not null,
  title text not null,
  constraint pri primary key (id_label),
  constraint unq unique (rank)
)

Не имеет значения, являются ли они PostgreSQL или MySQL, они демонстрируют одинаковое поведение. Запрос может выглядеть как select title from label order by rank. Предположим, что таблица содержит:

id_label rank title
1        10   Cow
2        20   Apple
3        45   Horse
4        60   Beer

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

update label
set rank = case when rank = 20 then 10 else 20 end
where id_label in (1,2)

Неа. Также:

update label
set rank = case when rank = 20 then rank - 10 else rank + 10 end
where id_label in (1,2)

И даже:

update label
set rank = 30 - rank
where id_label in (1,2)

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

Обходной путь, безопасный для использования с помощью ACID, заключается в следующем:

начать транзакцию
выберите ранги первой, второй записи и наивысшего (max) ранга в таблице (что, скорее всего, потребует объединение)
обновить первую запись до ранга = max + 1
обновить вторую запись до ранжирования первого
обновить первую запись до ранга секунды
совершить

Это просто невыразимо уродливое. Хуже того, чтобы отказаться от ограничения, обновить, а затем воссоздать ограничение. Предоставление таких привилегий оперативной роли требует неприятностей. Поэтому мой вопрос заключается в следующем: есть ли простая техника, которую я упустил, которая решает эту проблему, или я SOL?

Ответ 1

С PostgreSQL это может быть разрешено только "хорошим" способом с использованием Версии 9.0, потому что вы можете определить уникальные ограничения, которые можно отложить там.

С PostgreSQL 9.0 вы просто выполните:

create table label (
  id_label serial not null,
  rank integer not null,
  title text not null,
  constraint pri primary key (id_label)
);
alter table label add constraint unique_rank unique (rank) 
      deferrable initially immediate;

Затем обновление так же просто:

begin;
set constraints unique_rank DEFERRED;
update rank
   set rank = case when rank = 20 then 10 else 20 end
   where id_label in (1,2);
commit;

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

Ответ 2

Конечно, вы можете просто:

update label set rank = 5 where id_label=2

но проблема здесь, я думаю, вам нужно иметь возможность обрабатывать случай, когда нет "разрыва" между последовательными рядами. Для postgres использование numeric вместо integer обходит проблему, поскольку она почти неограниченная точность

create table label (
  id_label serial not null,
  rank numeric not null,
  title text not null,
  constraint pri primary key (id_label),
  constraint unq unique (rank)
)

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

Ответ 3

У меня была аналогичная проблема, и мое решение было следующим:

START TRANSACTION
SELECT * FROM label WHERE id_label IN(1,2)
Delete FROM label WHERE id_label IN(1,2)
INSERT INTO label(all, columns, of, table) VALUES(all, values, we, selected)
COMMIT TRANSACTION

Если какие-либо ошибки, откат транзакции.

Вы можете сделать это, не отбрасывая уникальное ограничение.