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

That's what:

I have a webapp built with jQuery / php / mysql. It is a kind of task manager. I have categories and tasks related to categories. Tasks have a surrogate primary key (auto-increment id) and for database consistency I have a natural key (unique) like: (id_category, position)

When the user moves a task, I have to update the position (ajax request) of that task in the DB, as well as the positions of all other tasks related to changes. For example, if you moved a job with position 5 to position 2, problems with positions 4 through 2 should increase their position by one. (This is my approach, I know that some people submit the entire set of assignments of this category with new positions, but I am trying to update only those that should be)

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

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

Thanks for any help.

+3


source to share


1 answer


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

      



Should do it.

+1


source







All Articles