oracle defered constraintITeye - 牛牛娱乐

oracle defered constraintITeye

2019年03月25日09时04分52秒 | 作者: 小萍 | 标签: 一个,发现,今日 | 浏览: 1658

今日搭档遇到一个问题,发现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,有空再查
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表牛牛娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章