Mysql temporarily suppress unique index

Question

I have a table with unique index on two columns, id_parent and sort_order to be precise

+----+-----------+------------+-------------+-------------+-------------+
| id | id_parent | sort_order | some_data   | other_data  | more_data   |
+----+-----------+------------+-------------+-------------+-------------+
| 1  |         1 |          1 | lorem ipsum | lorem ipsum | lorem ipsum |
| 2  |         1 |          2 | lorem ipsum | lorem ipsum | lorem ipsum |
| 3  |         1 |          3 | lorem ipsum | lorem ipsum | lorem ipsum |
+----+-----------+------------+-------------+-------------+-------------+

Now I want to update them, their data and their sort_order in one-go. sort_order would change from 1 - 2 - 3 to, for example 2 - 3 - 1.

But when I start running update statements, unique index block me, just as expected, saying that I can't have two rows with id_parent = 1 and sort_order = 2. Well, I could set it 4 for now, update other rows in correct order, and then set this one. But then, I would have to run an extra statement, and most probably add additional logic to my scripting language to determine correct order of updates. I also use ORM, and it becomes even more inconvinient.

My question now, is there some method to make mysql temporarily ignore this index? Like starting a special transaction, in which indexes would be calculated only right before commiting it?

Answer 1

As far as I know that isn't possible.

The only time I've seen anything like that is that you can disable non unique keys on myisam tables. But not on InnoDB and not on unique keys.

However, to save you an update or two, there is no need to have the exact number 1, 2 and 3. You could as well have 4, 5 and 6. Right? You would use it in a order by and nothing else so the exact numbers aren't important. It will even save you an update if you're clever. From your example

update table set sort_order = 4 where sort_order = 1 and id = 1 and id_parent = 1;

New sort order is 2, 3, 1. And in just one update.

Answer 2

‘But when I start running update statements…’ – I understand, you tried updating the values using multiple UPDATE statement, like in a loop. Is that so? How about updating them in one go? Like this, for example:

UPDATE atable
SET sort_order = CASE sort_order WHEN 3 THEN 1 ELSE sort_order + 1 END
WHERE id_parent = 1
  AND sort_order BETWEEN 1 AND 3

A single statement is atomic, so, by the time this update ends, the values of sort_order, although changed, remain unique.

I can't test this in MySQL, sorry, but it definitely works in SQL Server, and I believe the behaviour respects the standards.