How can I update a MySQL natural key while avoiding duplicate foreign keys?

Question

Here is the thing:

I have a webapp built with jQuery/php/mysql. It's kind of a task manager. I have categories and tasks belonging to categories. Tasks have a surrogate primary key (Autoincrement id) and for database consistency I have a natural key (Unique) like this: (id_category, position)

When a user moves a task I have to update the position (ajax request) of this task in DB but also the positions of all other tasks that are concerned by the changes. For example, if you move up a given task with position 5 to position 2, the tasks with positions from 4 to 2 must have their position increased by one. (This is my approach; I know some people send the whole set of tasks of the given category with the new positions but I am trying to update only the ones that need to be)

The problem is I don't know how to update the position of the tasks without avoiding a duplicate error in my natural key because I cannot / don't know how to update everything at the same time.

So could you help me with that or maybe should I change my approach. I could just remove the Unique index (natural key) but database consistency mechanisms are there for something...

Thank you for any help.

Answer 1

BEGIN TRANSACTION;
SET UNIQUE_CHECKS=0;
-- update 1
-- update 2
SET UNIQUE_CHECKS=1; 
COMMIT;

Should do it.