Prevent duplicates in MySql without using unique constraint I have a rules table consist of a column called priority, that must be gapless, incremental and unique.
A user must be able to re-order priority of the rules. For example, if there are 10 rules for a user and she moves the last rule to the 5th place, all of the other records between must get updated.
To prevent race condition during updates, I can use a select ... for update lock. But I can't find a way to guarantee the uniqueness of priority during inserts.
Firstly I wanted to use a deferred unique constraint, but figured out that MySql does not support this feature as Postgres do.
Then I thought about using a trigger on before insert, to calculate the priority, but I found that duplications still can show up.
What must I do? Is there any way to create a unique check that only works during inserts not updates?
Here's a complete test case, annotated:
CREATE TABLE t_rearr (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    priority INT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(priority)
) ENGINE=InnoDB;

INSERT INTO t_rearr (priority) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);


BEGIN;

SELECT GROUP_CONCAT(id ORDER BY priority) FROM t_rearr FOR UPDATE;  
-- Expect 1,2,3,4,5,6,7,8,9,10  
-- Note the FOR UPDATE  

-- 3 steps to swap:
-- First, move out of way (this assumes 0 is not a valid priority)
UPDATE t_rearr SET priority =  0 WHERE priority =  5;

UPDATE t_rearr SET priority =  5 WHERE priority = 10;

UPDATE t_rearr SET priority = 10 WHERE priority =  0;

COMMIT;

SELECT GROUP_CONCAT(id ORDER BY priority) FROM t_rearr;
# Expect 1,2,3,4,10,6,7,8,9,5