3

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.

Richard Simões
  • 12,401
  • 6
  • 41
  • 50
Kev
  • 5,049
  • 5
  • 32
  • 53
  • Why do you need your natural key when you have autoinc key? What purpose does it serve? – Toby Allen Mar 28 '12 at 18:54
  • 2
    In your example you could do something like: `UPDATE table SET position = -1 WHERE position = 5; UPDATE table SET position = position + 1 WHERE position < 5 AND position > 1; UPDATE table SET position = 2 WHERE position = -1` (assuming -1 is a position that will never be used) – NullUserException Mar 28 '12 at 18:55
  • @TobyAllen For database consistency. Two tasks cannot be at the same position in the same category. As I was building the app it happened some times because I didn't check some conditions. – Kev Mar 28 '12 at 18:58
  • I have done something similar (but without ajax), I will try remember and post. – Gabriel Santos Mar 28 '12 at 19:00
  • @NullUserException Thank you for your answer, I thought of something like this but I was wondering if this can be a problem with concurrent accesses. The app is not meant to be shared by various users but could be later. So this could lead to strange behavior, no? – Kev Mar 28 '12 at 19:00
  • 1
    @Amida Not if you use transactions. – NullUserException Mar 28 '12 at 19:01
  • Mysql doesnot "lock" when in transaction..? Doesn't remember.. – Gabriel Santos Mar 28 '12 at 19:02
  • Ok transaction is something new to me so I'll try it and provide feedback with results. Thank you :) – Kev Mar 28 '12 at 19:07
  • @amida maybe you should check the conditions! – Toby Allen Mar 29 '12 at 13:39
  • @TobyAllen My question is more about how to work with the natural key, I only explained what's doing my app in order to provide a context. It's at MySQL level that I have a problem. KingCrunch solution would be perfect if it worked with update (check my comments below). So as it is now, I use NullUserException proposal, but if you have a more "beautiful" solution I'll take it ! – Kev Mar 29 '12 at 20:39

1 Answers1

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

Should do it.

KingCrunch
  • 128,817
  • 21
  • 151
  • 173
  • I have been trying it but still get duplicate errors. It seems like SET UNIQUE_CHECKS is not working. I first started modifying my queries in PHP then tried to play in PHPMyAdmin. example: #task with id5 is in position 1 , task with id 41 is in position 2 START TRANSACTION; SET UNIQUE_CHECKS=0; UPDATE tasks SET position = 2 WHERE id =5; UPDATE tasks SET position = 1 WHERE id =41; SET UNIQUE_CHECKS=1; COMMIT; – Kev Mar 28 '12 at 20:57
  • After trying to find more information in mySQL documentation I only could find that SET UNIQUE_CHECKS=0 is used when you want to import (insert) a lot of information. Turning this off, speeds up the process because InnoDb won't check for uniqueness. But they don't say anything about using it with update so maybe your code cannot work actually. Have you tried it? Meanwhile I'll try with NullUserException solution. – Kev Mar 28 '12 at 22:04