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));
精彩评论