From: | Glen Huang <heyhgl(at)gmail(dot)com> |
---|---|
To: | Alban Hertroys <haramrae(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to create unique index on multiple columns where the combination doesn't matter? |
Date: | 2017-03-24 06:47:20 |
Message-ID: | 22709A59-3CE7-40F5-8914-C85F2114E1E5@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <haramrae(at)gmail(dot)com> wrote:
>
>>
>> On 22 Mar 2017, at 17:54, Glen Huang <hey(dot)hgl(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | lin | 2017-03-24 09:03:54 | postgres source code function "internal_ping" may be not right in some conditions |
Previous Message | Michael Paquier | 2017-03-24 06:46:52 | Re: Lag in asynchronous replication |