mysql reorder rows with unique constraint

Question

I'm having some trouble coming up with an efficient solution to this problem. Maybe I am making it more complicated than needs to be. I have a table like this:

thing_id | user_id | order
    1         1        0
    2         1        1
    3         1        2

The user may mess around with their things and it may happen that they change thing 1 to thing 3, and thing 3 to thing 1. In my case, it is not that the user is explicitly changing the order. Rather, they are modifying their bank of things, and they may change the thing in slot 1 to be the thing in slot 3, and vice versa. So if the user performs this operation, the table should look like this:

thing_id | user_id | order
    3         1       0
    2         1       1
    1         1       2

What complicates this is that (thing_id, user_id) has a unique constraint, so doing sequential updates does not quite work. If I try to UPDATE tbl SET thing_id=3 WHERE thing_id=1, the unique constraint is broken.

The order column is purely for show, in order to make an alphabetized list. So I suppose I could use PHP to check the order and figure things out like that, but this introduces code that really has nothing to do with the important stuff. I'd like to find a solution that is purely/mostly SQL.

Also, along the same lines, if I were to insert a new row into the table, I would want the order value to be 3. Is there an efficient way to do this in SQL, without first having to SELECT MAX(order) WHERE user_id=1?

Regarding the last question, you can use the `SELECT MAX(ORDER)` as part of the INSERT, as a sub query, at least no need to make two calls and possibly add a race condition if not using transactions. — ficuscr, Mar 01 '13 at 17:18
Ok cool. I'm using the codeigniter framework so I may have to fight with that a bit so it doesn't think it is a SQL injection haha. So the query would simply be `INSERT INTO tbl VALUES( 4, 1, (SELECT MAX(order) FROM tbl WHERE user_id=1)` , right? — parker.sikand, Mar 01 '13 at 17:20
Yup. In fact using [transactions](http://dev.mysql.com/doc/refman/5.0/en/commit.html) might address your main issue... read this: http://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit ... Well actually not transactions but deferring referential integrity. — ficuscr, Mar 01 '13 at 17:20
I think your client-side code is actually a better place for dealing with order than your SQL. In general, operations that are strictly about presentation don't below in your database layer. — Jeff Rosenberg, Mar 01 '13 at 17:21
@Jeff, I would disagree. My rule is leverage the DB whenever possible and loop as little as you can in code. — ficuscr, Mar 01 '13 at 17:22
@ficuscr I'm with you. I'd rather spend time on complex SQL if it makes my code simpler. But perhaps I will need to ponder on an alternative design. — parker.sikand, Mar 01 '13 at 17:24
Why not add another column, eg, desired_order and update that, instead of messing with your unique constraints? — hd1, Mar 01 '13 at 17:27
@parker.sikand Turning into a chat here... But, I think you are correct it seems to not be optimal design. What you describe is not really that complex. Take a step back maybe and it will just click. — ficuscr, Mar 01 '13 at 17:27
What do you mean by "the order column is purely for show"? Does that mean you cannot do a "update tbl set order=? where thing_id=?" because the DB entity is not under your control? — marty, Mar 01 '13 at 17:28

score 0 · Answer 1 · answered Mar 01 '13 at 17:47

0

My comment seems to have gotten some traction, so I'm posting it as an answer... To avoid your problem, add a new column, without constraints, and just use that for user desired updates.

answered Mar 01 '13 at 17:47

hd1

33,938
5
80
91

Gordon Linoff · Answer 2 · 2013-03-01T18:38:28.620

Why aren't you updating the order instead of the thingid?

UPDATE tbl
    SET order = 2
    WHERE thing_id=1;

Each row represents a "thing-user" pair. The data is the ordering that you want to use. You don't want to change the entity ("thing-user"). You want to change the data.

By the way, you'll then have to do some additional work to keep unique values in orders.

If you switched this around and put the unique constraint on user_id, order, then it would make sense to update the thing_id.

mysql reorder rows with unique constraint

2 Answers2