Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 the 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.

like image 557
Svein Bringsli Avatar asked Oct 09 '11 15:10

Svein Bringsli


1 Answers

CREATE UNIQUE INDEX unique_cd0_cd1
      ON VARIANTCOMBINATIONRULE (least(VARRECID0, VARRECID1), greatest(VARRECID0, VARRECID1));
like image 133
a_horse_with_no_name Avatar answered Sep 19 '22 12:09

a_horse_with_no_name