phpmysqlunique-constraintreorderlist

Updating table with unique column

A table contains the following data, is using INNODB, has a UNIQUE constraint on position/fk, and doesn't allow NULL for position.

+----+----------+-----+
| id | position | fk  |
+----+----------+-----+
|  1 |        1 | 123 |
|  2 |        2 | 123 |
|  3 |        3 | 123 |
|  4 |        4 | 123 |
|  5 |        5 | 123 |
|  6 |        6 | 123 |
|  7 |        7 | 123 |
|  8 |        8 | 123 |
|  9 |        9 | 123 |
| 10 |       10 | 123 |
+----+----------+-----+

PHP receives a request to update the table to the following. The format of the request can be provided how ever is most convenient such as [2,1,4,3,6,5,8,7,10,9] or [{"id":1, "position":2}, ... ], etc. 

+----+----------+-----+
| id | position | fk  |
+----+----------+-----+
|  1 |        2 | 123 |
|  2 |        1 | 123 |
|  3 |        4 | 123 |
|  4 |        3 | 123 |
|  5 |        6 | 123 |
|  6 |        5 | 123 |
|  7 |        8 | 123 |
|  8 |        7 | 123 |
|  9 |       10 | 123 |
| 10 |        9 | 123 |
+----+----------+-----+

I've confirmed that SET unique_checks=0; will not allow unique checks to be temporarily disabled, and don't wish to actually remove the unique index, update the table, and reapply the unique index.

How can this table be updated?

If there is no simple means to do so, I thought of a couple of options, but don't like them:

  1. Allowing NULL in position. Is there a way to temporarily allow NULL similar to how SET FOREIGN_KEY_CHECKS=0; can disable foreign keys?
  2. First delete all the records and then reinsert them. This might result in performance issues as there are indexes on the table which will need to be recreated.
Solution

  • All I can think is that you need to first change all the positions to some other values that aren't in the range of new position values you ultimately need to set, but are still unique within the rows.

    An easy way to do this, assuming your position column is a signed integer, is to set all the positions to their opposite (negative) value. They'll remain unique, but they won't be in the set of the new values.

    You can do this in a transaction along with your subsequent updates, so no other concurrent transaction will ever see the negative values.

    BEGIN;
UPDATE MyTable SET position = -position;
UPDATE MyTable SET position = 2 WHERE id = 1;
...etc... 
COMMIT;

    This is a hack. The sign bit of the integer is being used for a purpose other than showing negative numbers.