Reputation: 2366
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?
Upvotes: 20
Views: 20468
Reputation: 58530
For MyISAM tables, you can simply add this line at the start of your script:
SET UNIQUE_CHECKS=0;
It's common to use this in conjunction with:
SET FOREIGN_KEY_CHECKS=0;
The UNIQUE_CHECKS
variable is mentioned in the docs here:
http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html
There seem to be reports that the above commands don't work with the InnoDB engine. If so, then you can try dropping the UNIQUE
index temporarily and then adding it back.
See: How to remove unique key from mysql table
Feel free to edit this post and improve it with a code example if you have one.
Upvotes: 7
Reputation: 77707
‘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.
Upvotes: 5
Reputation: 40061
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.
Upvotes: 6