4

I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be rejected. How can I achieve that?

My table definition:

CREATE TABLE mytable (
    id serial primary key,
    col1 int NOT NULL,
    col2 int NOT NULL,
    unique (col1, col2)
)
2

1 Answer 1

7

This unicity constraint can be enforced with this unique index:

CREATE UNIQUE INDEX idxname ON mytable(least(col1,col2),greatest(col1,col2));

Demo:

test=> insert into mytable (col1,col2) values(1,2);
INSERT 0 1
test=> insert into mytable (col1,col2) values(2,1);
ERROR:  duplicate key value violates unique constraint "idxname"
DETAIL:  Key ((LEAST(col1, col2)), (GREATEST(col1, col2)))=(1, 2) already exists.
5
  • Very smart, indeed.
    – Renzo
    Commented Jun 28, 2015 at 16:48
  • I am constantly in awe of PostgreSQL - I'll be using it in future. This is a really nice answer and pretty cool! I thought that you'd have to jump through all sorts of hoops for this one! Am I right in thinking that not even Oracle has this capability? +1.
    – Vérace
    Commented Jun 28, 2015 at 20:49
  • @Vérace: While I obviously agree with you on Postgres, Oracle also has "function-based indexes". They have trouble with NULL values, though (which they did not implement properly): Oracle Database will not use the index unless the query filters out nulls Commented Jun 28, 2015 at 23:39
  • @ErwinBrandstetter - how does PostgreSQL cope with NULLs in this scenario?
    – Vérace
    Commented Jun 29, 2015 at 0:09
  • @Vérace: NULL values are indexed like any other value. Not relevant for this question since both columns are defined NOT NULL. More: dba.stackexchange.com/q/9759/3684. If NULL was allowed for this special case, there would be more considerations since GREATEST and LEAST both prioritize non-null values over NULL ... Commented Jun 29, 2015 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.