Yes, the order doesn't matter, and this approach sounds like a good idea. I'll try it out, thanks.
> On 23 Mar 2017, at 3:56 PM, Alban Hertroys <haram...@gmail.com> wrote: > >> >> On 22 Mar 2017, at 17:54, Glen Huang <hey....@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/UPDATE to 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.
