Re: [GENERAL] How to create unique index on multiple columns wherethe combination doesn't matter?

> On 22 Mar 2017, at 17:54, Glen Huang <hey.hgl@gmail.com> wrote:
>
> Hello,
>
> If I have a table like
>
> CREATE TABLE relationship (
>  obj1 INTEGER NOT NULL REFERENCES object,
>  obj2 INTEGER NOT NULL REFERENCES object,
>  obj3 INTEGER NOT NULL REFERENCES object,
>  ...
> )
>
> And I want to constrain that if 1,2,3 is already in the table, rows like 1,3,2 or 2,1,3 shouldn't be allowed.
>
> Is there a general solution to this problem?

Does the order of the values of (obj1, obj2, obj3) in relationship matter? If not, you could swap them around on
INSERT/UPDATEto be in sorted order. I'd probably go with a BEFORE INSERT OR UPDATE trigger. 

In addition, to prevent unsorted entry, on obj2 add CHECK (obj2 > obj1) and on obj3 add CHECK (obj3 > obj2).

Now you can create a normal PK or unique key on (obj1, obj2, obj3) as the order of their values is not variable
anymore.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.