PostgreSQL – Can Column Uniqueness Be Commutative?

constraintpostgresqlunique-constraint

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

Best Answer

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.
Related Question