Re: [GENERAL] How to create unique index on multiple columns wherethe combination doesn't matter?
От | Andreas Kretschmer |
---|---|
Тема | Re: [GENERAL] How to create unique index on multiple columns wherethe combination doesn't matter? |
Дата | |
Msg-id | 20170323060144.GA5930@tux обсуждение исходный текст |
Ответ на | [GENERAL] How to create unique index on multiple columns where the combination doesn't matter? (Glen Huang <hey.hgl@gmail.com>) |
Список | pgsql-general |
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? Sure. test=*# create extension intarray; CREATE EXTENSION test=*# create table foo(c1 int, c2 int, c3 int); CREATE TABLE test=*# create unique index index_unique_foo on foo(sort(array[c1,c2,c3],'asc')); CREATE INDEX test=*# insert into foo values (1,2,3); INSERT 0 1 test=*# insert into foo values (3,2,1); FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »index_unique_foo« DETAIL: Schlüssel »(sort(ARRAY[c1, c2, c3], 'asc'::text))=({1,2,3})« existiert bereits. test=*# (sorry for german messages, it means error, dublicate entry ...) Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
В списке pgsql-general по дате отправления: