oracle defered constraint

今天同事遇到一个问题，发现UI可能需要交互一个有唯一性约束的column的两个值，比如叫id，一个值是1，一个是2，UI传过来的是两句update(其实是一个batch里面做的)。由于是同一个transaction，就会报错。解决办法是参考了http://www.rhinocerus.net/forum/databases-oracle-misc/327785-problem-updating-unique-values.html:

This works fine on 9i:

CREATE TABLE primes (
rank INT NOT NULL PRIMARY KEY,
prime INT NOT NULL);

INSERT INTO primes VALUES (1,3);
INSERT INTO primes VALUES (2,2);
COMMIT;

ALTER TABLE primes
ADD CONSTRAINT unique_prime
UNIQUE (prime) DEFERRABLE INITIALLY DEFERRED;

UPDATE primes SET prime = 2 WHERE rank = 1;
UPDATE primes SET prime = 3 WHERE rank = 2;
COMMIT;

Without deferrable constraint checking, this would also work:

UPDATE primes
SET prime = CASE rank WHEN 1 then 2 ELSE 3 END
WHERE rank IN (1,2);

自己去试的时候发现
UPDATE primes SET prime = 2 WHERE rank = 1;
UPDATE primes SET prime = 3 WHERE rank = 2;
COMMIT;
可以成功，但是
UPDATE primes SET prime = 2 WHERE prime = 3;
UPDATE primes SET prime = 3 WHERE prime = 2;
COMMIT;
这样发现不行，好奇怪，先mark，有空再查