[MySQL] Zmniejszenie wartości kolumny o 1

Cześć. Sprawa wygląda następująco. Posiadam pewną tabelę. Znajduje się w niej kolumna, która jest ustawiona jako unique. Znajdują się w niej liczby od 1 do X. Ta kolumna służy mi do tego aby sortować na stronie WWW ręcznie rekordy z tej tabeli.

Mam napisaną taką funkcjonalność, która umożliwia usuwanie danych z tej tabeli. Jeśli usuwam rekord, który w kolumnie z sortowaniem miał wartość na przykład 6, chcą aby kolumny z wartością > 6 zmniejszyły się o 1 aby zachować spójność w sortowaniu.

W tym celu wykonuję takie zapytania:

                        $db->query("START TRANSACTION") or die($db->error);
                        $q1 = $db->query("DELETE FROM objects WHERE object_id = '".$id."'") or die($db->error);
                        $q2 = $db->query("UPDATE objects SET object_order = object_order - 1 WHERE object_order > '".$order."'") or die($db->error);
                        
                        if($q1 && $q2) {
                            $db->query("COMMIT") or die($db->error);
                        } else {
                            $db->query("ROLLBACK") or die($db->error);
                        }

Niestety. Pierwsze zapytanie dostaje wartość true, drugie wywala błąd "Duplicate entry for key ...". Zmienna $order ma prawidłową wartość więc problem leży w bazie danych. Oczywiście sprawdziłem zawartość tej kolumny i nie występuje żaden duplikat.

Zapytanie "UPDATE cms_objects SET object_order = object_order - 1" użyte w phpMyAdmin wyrzuca taki sam błąd.

Co robię nie tak? Nie da się tak zmniejszać kolumny, która ma indeks Unique?

Trochę to bez sensu. Co zyskasz przenumerowując ?
Tak się nie robi ...
Przecież po usunięciu wiersza z tabeli bez przenumerowania, sortowanie również będzie działać

Ma to sens bo nie mam przerw między cyframi w tej kolumnie po usunięciu.
A jak mam funkcje od sortowania to wykorzystuję zmienne $order_actual oraz $order_previous = $order_actual - 1.

To przecież musi tak działać. Pytanie dlaczego nie chce.

Zdaje się, że mamy do czynienia z typowym problemem X/Y - dlaczego zaprojektowałeś Twoje sortowanie tak, że "nie może być dziur"?
W jaki sposób sortujesz te obiekty?

(btw, actual znaczy prawdziwy - słowo, które powinieneś wykorzystać, to current)

@Kisialala:
jak napisał @Patryk27
Twoim problemem jest sam algorytm sortowania

@Patryk27: Pozwolę sobie się nie-do-końca zgodzić z tym, co napisałeś odnośnie słowa actual :P

Nawet jakbym się zgodził na dziury to nie chcę dopuścić do takiej sytuacji, że wiele rekordów będzie miało tą samą wartość order, np 2.

Dziury w numeracji a duplikaty coś zupełnie innego.
Samo usuwanie wierszy nie stworzy duplikatów
Uważam że walczysz z problemem który sam sobie stworzyłeś przez niezbyt szczęśliwy algorytm sortowania, który wymaga stałego i równego jeden kroku w polu będącym argumentem sortowania

Usuwanie nie ale funkcje sortujące już tak.

Nie dyskutujmy na temat mojej metody sortowania. Wyjaśnijcie proszę dlaczego nie da się zmniejszać wartości kolumny z indeksem Unique nawet poprzez phpMyAdmin. A jeśli się da to czemu to nie chce działać?

Usuwanie nie ale funkcje sortujące już tak.

dobrze widzę ???funkcje sortujące tworzą duplikaty ??

Oto zrzut kawałka tabeli. Może to Wam coś podpowie.

-- phpMyAdmin SQL Dump
-- version 4.4.12
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Czas generowania: 04 Mar 2019, 21:47
-- Wersja serwera: 5.6.20
-- Wersja PHP: 5.5.15

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Baza danych: `xxx`
--

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `cms_objects`
--

CREATE TABLE IF NOT EXISTS `cms_objects` (
  `object_id` int(10) unsigned NOT NULL,
  `object_order` int(10) unsigned NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8;

--
-- Zrzut danych tabeli `cms_objects`
--

INSERT INTO `cms_objects` (`object_id`, `object_order`) VALUES
(23, 1),
(14, 2),
(16, 3),
(22, 4),
(10, 5),
(17, 6),
(25, 7),
(15, 8),
(24, 9),
(20, 10);

--
-- Indeksy dla zrzutów tabel
--

--
-- Indexes for table `cms_objects`
--
ALTER TABLE `cms_objects`
  ADD PRIMARY KEY (`object_id`),
  ADD UNIQUE KEY `object_order` (`object_order`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT dla tabeli `cms_objects`
--
ALTER TABLE `cms_objects`
  MODIFY `object_id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=56;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

@Kisialala:
spróbuj dodać do update klauzlę order by object_order

O matko. Taka głupota :D Działa pięknie :)

@Kisialala:
to teraz zastanów się dlaczego z klauzulą where działa a bez niej nie

edt.
oczywście miało być order

Działa już w obu przypadkach. Dokonywało update w kolejności id rekordów a nie kolumny order i trafiało na duplikat.

Dziękuję za pomoc!

@Kisialala:
Bez order działało w kolejności przypadkowej, bez żadnej gwarancji porządku

Zakładając, że w $order masz wartość pola OBJECT_ORDER dla usuwanego rekordu $id
oraz, że w OBJECT_ORDER mamy np.

10
11
12
13

to problem może wynikać ze sposobu aktualizacji danych w kolumnie i tego raczej nie przeskoczysz.

Skoro od OBJECT_ORDER odejmujesz jeden, a nie masz wpływu na kolejność wykonywania tej operacji,
to może się zdarzyć, że najpierw zostanie wykonane:

OBJECT_ORDER = 12 - 1 = 11

a taka wartość jest w tabeli i masz naruszenie unikalności.

To by mogło zadziałać, gdyby baza aktualizowała rekordy zaczynając od najmniejszej wartości.
Ale nie masz gwarancji jak ona to zrobi.

Tak czy inaczej, daruj sobie to aktualizowanie. Dziury w numeracji w niczym nie przeszkadzają, skoro masz unikalność na tym polu.
Poza tym jest to skrajnie nieefektywne, przy dużej liczbie rekordów to by tylko zamulało bazę.

"nie chcę dopuścić do takiej sytuacji, że wiele rekordów będzie miało tą samą wartość order, np 2."

Skoro pole jest unikalne, to do takiej sytuacji baza nie dopuści.