How to perform multiple updates with a unique index in MySQL

Question

I have the following table with a unique index by field "position_in_list":

  CREATE TABLE `planned_operation` (
    `id` bigint(20) NOT NULL,
    `position_in_list` bigint(20) NOT NULL,
    `name` varchar(255) not null
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

  ALTER TABLE `planned_operation`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `position_in_list` (`position_in_list`);

  ALTER TABLE `planned_operation`
    MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

  INSERT INTO `planned_operation` (`id`, `position_in_list`, `name`) VALUES
  (1, 1, 'first'),
  (2, 2, 'second');

Then I have a trivial task, this is a change in position when updating the list. Accordingly, you need to update the list of items before which the record was inserted. In order not to perform thousands of updates, I execute one query:

update planned_operation 
set position_in_list = case position_in_list 
    when 2 then 3
    when 1 then 2
end
where position_in_list in (1, 2)

But when executing an error is issued:

#1062 - Duplicate entry '1' for key 'position_in_list'

Is there any way to avoid an error? Without disabling the unique index

The Impaler · Accepted Answer · 2019-04-18T14:35:51.007

2

You want deferrable constraints.

Unfortunately, MySQL does not implement deferrable constraint checks -- an integral part of SQL that few database engines implement.

As far as I know only PostgreSQL and Oracle (partial) do implement them.

In simple words, this means that MySQL checks the unique constraint on every single row change inside an UPDATE statement. With deferrable constraints you could defer this check to the end of the statement, or even to the end of the database transaction.

Now, you would need to switch to PostgrSQL or Oracle to defer contraints checks to the end of the statement (as you seem to want). I guess that's way out of the scope for you, but it's a theoretical option.

For a more in depth discussion you could look into Deferrable Constraints answer.

edited Apr 18 '19 at 14:35

answered Apr 18 '19 at 14:23

The Impaler

45,731
9
39
76

So instead of setting both values to `null`, which doesn't trigger unique key constraint, then update each column to desired position - swapping the database is the answer? Sure, MySQL misses the syntactic sugar you described, but surely it's easier to type out ~4 lines of SQL than swap the entire db vendor, no? – Mjh Apr 18 '19 at 15:32
@Mjh I agree with you. It's not realistic to change the engine. However, setting the values to null may also be not desirable. – The Impaler Apr 18 '19 at 15:34
Set values to null first, then update each to desired value. That way unique constraint isn't violated and desired outcome is achieved. It's not overly complex, but it's not as easy as using Postgres for the same action. – Mjh Apr 18 '19 at 18:26

How to perform multiple updates with a unique index in MySQL

1 Answers1