Unique constraint on different columns

Question

I'm trying to make a table to store the edges of a directed graph, but want to avoid duplicate edges in both directions. I can of course make it one direction :

CREATE TABLE edge (parent integer, child integer, UNIQUE(parent, child));

But how can I make sure that I also don't allow a new (parent, child) that already exists as (child, parent) ?

Any ideas would be greatly appreciated !

Answer 1

Using GREATEST and LEAST

Essentially, if we can sort of the columns in the index, we can ensure that the duplicate entry causes a collision and a constraint violation this is because sort(5,2) = sort(2,5). Because there is no sort, we use greatest and least.

CREATE TABLE edge (
  parent int,
  child  int
);

CREATE UNIQUE INDEX
  ON edge ( greatest(parent,child), least(parent,child) );

INSERT INTO edge(parent,child)
VALUES (42,7), (42,9), (5,7);

INSERT INTO edge(parent,child)
VALUES (7,42);
ERROR:  duplicate key value violates unique constraint "edge_greatest_least_idx"
DETAIL:  Key ((GREATEST(parent, child)), (LEAST(parent, child)))=(42, 7) already exists.

Answer 2

You can also do this: (for positive INTEGERs db-fiddle)

CREATE TABLE edge 
(
  parent int NOT NULL,
  child  int NOT NULL
);

CREATE UNIQUE INDEX ed_uq_ix ON edge (ABS(parent - child), (parent * child));

Non conflicting records:

INSERT INTO edge(parent,child) VALUES (42,7), (42,9), (5,7); -- succeeds

BUT:

INSERT INTO edge VALUES (9, 42);  -- fails!