MySQL: Avoid having to set a NULL value on an UNIQUE field before updating it-CodePudding

I have an SQL table with two fields: id and order.

CREATE TABLE IF NOT EXISTS article (
  `id`        INT(11) NOT NULL AUTO_INCREMENT,
  `order`     INT(11) UNIQUE,
  PRIMARY KEY (`id`)
);

In this table I have some items:

 ---- ------- 
| id | order |
 ---- ------- 
|  1 |     0 |
|  2 |     1 |
|  3 |     2 |
|  4 |     3 |
|  5 |     4 |
|  6 |     5 |
|  7 |     6 |
|  8 |     7 |
|  9 |     8 |
| 10 |     9 |
 ---- -------

Now I want to change order position of one item: element with id 3 (order position 2) will move into position 6. Thus, the elements between position 4 and 6 (this last one included) will have to decrease their order field. The result should be this:

 ---- ------- 
| id | order |
 ---- ------- 
|  1 |     0 |
|  2 |     1 |
|  4 |     2 | ⌉
|  5 |     3 | |
|  6 |     4 | | Updated items
|  7 |     5 | |
|  3 |     6 | ⌋
|  8 |     7 |
|  9 |     8 |
| 10 |     9 |
 ---- -------

Of course the first update —update item with id 3 with order field 6— is easy:

UPDATE article
SET article.order = 6
WHERE id = 1;

Then I can decrease the items between positions greater than 2 and lower or euql than 6:

UPDATE article
SET article.order = article.order -1
WHERE 
  article.order > 2
  AND
  article.order <= 6
;

But here there is a problem: order field is UNIQUE. So I have to first set it to NULL for the item I want to move:

UPDATE article
SET article.order = NULL
WHERE id = 3;

UPDATE article
SET article.order = article.order -1
WHERE 
  article.order > 2
  AND
  article.order <= 6
;


UPDATE article
SET article.order = 6
WHERE id = 3;

Is there a way to avoid setting this NULL? Here is a fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=342d714e88e68d3c4c6e0ca1ec8efa6c

CodePudding user response：

Is there a way to avoid setting this NULL?

Not in MySQL. The SQL Standard defines the feature of deferrable constraints that address this specific issue. Unfortunately, MySQL does not implement this section of SQL Specification. Nulls are your only option here.

Now, when a contraint is marked as deferrable (as in PostgreSQL or Oracle), its validation can be deferred to the end of each SQL statement execution, or even to the end of the whole transaction; that is, its full validation happens only at the time of the commit, after all updates have been done and all values would be good again.

As you see, unless you have the option to migrate to PostgreSQL or Oracle (highly unlikely) you are stuck with using nulls.

CodePudding user response：

I would ask myself whether the unique constraint was actually required? Is any functionality dependent on the order value being unique? Is the extra overhead of the constraint necessary? If not -

UPDATE `article`
SET `order` = IF(`id` = 3, 6, `order` - 1)
WHERE `order` BETWEEN 2 AND 6;

db<>fiddle

CodePudding user response：

Probably not in 1 update because of the unique key.

But it can be done in 2 updates.

With the help of some variables.
And a descending sort, to get the NULL last.

--
-- move id 3 to position 6
--
SET @id := 3;
SET @orig := (select `order` from article where id = @id);
SET @dest := 6;
-- select @id id,@orig orig ,@dest dest;

UPDATE article 
SET `order` = NULL
WHERE id = @id;

UPDATE article 
SET `order` =  
    case 
    when  @orig < @dest and `order` between @orig and @dest 
     then `order` - 1
    when  @orig > @dest and `order` between @dest and @orig 
     then `order`   1
    when `order` is null then @dest
    else `order`
    end
WHERE (
      `order` is null 
   OR `order` between least(@orig,@dest) and greatest(@dest,@orig)
)
ORDER BY if(@orig>@dest,1,-1)*`order` DESC;

Demo on db<>fiddle here