How can I update my MySQL natural key while avoiding duplicate foreign keys?
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.
source to share