开发者

In Oracle, how do I declare a unique constraint over a pair of values AND it's reverse pair

I have a table that defines th开发者_StackOverflow中文版e rule between two variants:

CREATE TABLE VARIANTCOMBINATIONRULE
(
  VARRECID0  NUMBER(10)    NOT NULL,
  VARRECID1  NUMBER(10)    NOT NULL,
  RULE       NUMBER
);

ALTER TABLE VARIANTCOMBINATIONRULE ADD (
  CONSTRAINT VARIANTCOMBINATIONRULE_PK
  PRIMARY KEY
  (VARRECID0, VARRECID1));

How do I create a constraint that disallows "reversed pairs"? With this I mean that if there's a record for variants 12 and 14, a new record for the reversed pair (14 and 12) shouldn't be allowed.


CREATE UNIQUE INDEX unique_cd0_cd1
      ON VARIANTCOMBINATIONRULE (least(VARRECID0, VARRECID1), greatest(VARRECID0, VARRECID1));
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜