1

I am currently implementing a simple database schema (in PostgreSQL 11.1 (Debian)), with two int columns (say col1, col2).

My goal is to prevent duplicates appearing in any of the two columns. If (1,2) already existed, then (1,2) or (2,1) would cause a "duplicate key value violates unique constraint" error.

For a practical example. I have created a table someTable, with col1, col2, with a unique constraint (col1, col2). Everything is fine when I insert (1,2) a second time:

CREATE TABLE someTable (
 col1 int NOT NULL,
 col2 int NOT NULL,
 unique (col1, col2));

- CREATE TABLE

INSERT INTO someTable (col1, col2) VALUES (1,2);
- INSERT 0 1

INSERT INTO someTable (col1, col2) VALUES (1,2);
- ERROR:  duplicate key value violates unique constraint "sometable_col1_col2_key"
DETAIL:  Key (col1, col2)=(1, 2) already exists.

INSERT INTO someTable (col1, col2) VALUES (2,1);
- INSERT 0 1

I would like the (col1, col2) unique constraint to work for swapped values (for example 2,1 is treated the same as 1,2).

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
chocksaway
  • 111
  • 3
  • What is the purpose of preventing the duplicate where the values are reversed? It should be possible but will probably be a good size performance hit to implement especially as the table grows in size. – Joe W Sep 05 '19 at 15:01
  • For my project, each of the columns is a reference to a User (so User 1, and User 2) is the same as (User 2, and User 1). I want to avoid this unnecessary duplication. – chocksaway Sep 05 '19 at 15:10
  • You might be interested in this answer. p.s. welcome to the forum! 8-) – Vérace Sep 05 '19 at 23:05

2 Answers2

2

Enforce a particular canonical ordering on the values:

alter table sometable add constraint sdljfsdfj check (col1<=col2);

If they are in the wrong order, the check constraint will reject them. They are in the correct order the unique constraint will.

You could couple this with a BEFORE INSERT trigger to swap the order, if you don't want to force the application to deal with that logic.

jjanes
  • 39,726
  • 3
  • 37
  • 48
2

You can create an index on an expression that always puts the values into the same order:

create unique index on sometable (least(col1, col2), greatest(col1, col2));