Swap Unique Indexed Column Values in Database

Swap unique indexed column values in database

I think you should go for solution 2. There is no 'swap' function in any SQL variant I know of.

If you need to do this regularly, I suggest solution 1, depending on how other parts of the software are using this data. You can have locking issues if you're not careful.

But in short: there is no other solution than the ones you provided.

Swapping values with unique constraint in Entity Framework

It is not problem of EF but the problem of SQL database because update commands are executed sequentially. Transaction has nothing to do with this - all constrains are validated per command not per transaction. If you want to swap unique values you need more steps where you will use additional dummy values to avoid this situation.

How to swap values of two rows in MySQL without violating unique constraint?

Is it possible to accomplish this in MySQL without using bogus values and multiple queries?

No. (none that I can think of).

The problem is how MySQL processes updates. MySQL (in difference with other DBMS that implement UPDATE properly), processes updates in a broken manner. It enforces checking of UNIQUE (and other) constraints after every single row update and not - as it should be doing - after the whole UPDATE statement completes. That's why you don't have this issue with (most) other DBMS.

For some updates (like increasing all or some ids, id=id+1), this can be solved by using - another non-standard feature - an ORDER BY in the update.

For swapping the values from two rows, that trick can't help. You'll have to use NULL or a bogus value (that doesn't exist but is allowed in your column) and 2 or 3 statements.

You could also temporarily remove the unique constraint but I don't think that's a good idea really.

---

So, if the unique column is a signed integer and there are no negative values, you can use 2 statements wrapped up in a transaction:

START TRANSACTION ;
    UPDATE tasks 
    SET priority = 
      CASE
        WHEN priority = 2 THEN -3 
        WHEN priority = 3 THEN -2 
      END 
    WHERE priority IN (2,3) ;

    UPDATE tasks 
    SET priority = - priority
    WHERE priority IN (-2,-3) ;
COMMIT ;

How to swap value pairs restricted by unique index

So, you have something like this:

Participant temp = pair1.getParticipant();
pair1.setParticipant(pair2.getParticipant());
pair2.setParticipant(temp);

Instead, just do the following:

Participant temp1 = pair1.getParticipant();
Participant temp2 = pair2.getParticipant();
pair2.setParticipant(null);
session.flush();
pair1.setParticipant(temp2);
pair2.setParticipant(temp1);

The intermediate flush will make sure only one pair at a time references a participant.

How to perform multiple updates with a unique index in MySQL

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.

---

Related Topics