> 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
