Can column uniqueness be commutative?

Question

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)
)

Prior question for the same on SO. Similar question for three columns. For more columns you could have a unique expression index on a sorted array or a jsonb value with a JSON array that is sorted internally automatically. — Erwin Brandstetter, Jun 28 '15 at 23:47
@ErwinBrandstetter 'permutations' was the keyword that I should have used. Thanks. — user4150760, Jun 29 '15 at 05:23

score 7 · Accepted Answer · answered Jun 28 '15 at 16:42

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.

answered Jun 28 '15 at 16:42

Daniel Vérité

31,182
3
72
80

Very smart, indeed. – Renzo Jun 28 '15 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 Jun 28 '15 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 – Erwin Brandstetter Jun 28 '15 at 23:39
@ErwinBrandstetter - how does PostgreSQL cope with NULLs in this scenario? – Vérace Jun 29 '15 at 00:09
@Vérace: NULL values are indexed like any other value. Not relevant for this question since both columns are defined NOT NULL. More: http://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 ... – Erwin Brandstetter Jun 29 '15 at 00:39

Can column uniqueness be commutative?

1 Answers1

Linked

Related