StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAtomic multi-row update with a unique constraint
text
Body
copied!<p>I have a table of labels that are displayed in a ranked order. To ensure that no two rows can have the same rank, their values are unique:</p> <pre><code>create table label ( id_label serial not null, rank integer not null, title text not null, constraint pri primary key (id_label), constraint unq unique (rank) ) </code></pre> <p>Doesn't matter if it's PostgreSQL or MySQL, they exhibit the same behaviour. A query might look like <code>select title from label order by rank</code>. Assume the table contains:</p> <pre><code>id_label rank title 1 10 Cow 2 20 Apple 3 45 Horse 4 60 Beer </code></pre> <p>Now suppose I want to reorder two labels, e.g. have Apple ranked before Cow. The easiest way is to swap their rank values:</p> <pre><code>update label set rank = case when rank = 20 then 10 else 20 end where id_label in (1,2) </code></pre> <p>Nope. Nor:</p> <pre><code>update label set rank = case when rank = 20 then rank - 10 else rank + 10 end where id_label in (1,2) </code></pre> <p>Nor even:</p> <pre><code>update label set rank = 30 - rank where id_label in (1,2) </code></pre> <p>Each time, the unique constraint fires on the first row update and aborts the operation. If I could defer the check until the end of the statement I would be fine. This happens on both PostgreSQL and MySQL.</p> <p>An ACID-safe workaround is to:</p> <ol> <li>begin transaction</li> <li>select ranks of first, second record, and highest (max) rank in table (which offhand will probably require a union)</li> <li>update first record to rank = max + 1</li> <li>update second record to rank of first</li> <li>update first record to rank of second</li> <li>commit</li> </ol> <p>That's just unspeakably ugly. Worse is to drop the constraint, update, and then recreate the constraint. Granting such privileges to an operational role is asking for trouble. So my question is this: is there a simple technique I have overlooked that solves this problem, or am I SOL?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload