SQL unique combination of two columns and vice versa

Question

I have the following table:

ID
KEY_1
KEY_2

I want the combination of KEY_1 with KEY_2 to be unique and vice versa.

ALTER TABLE dbo.table_name
  ADD CONSTRAINT uq_table_name UNIQUE(KEY_1, KEY_2);

This way I can make the combination between KEY_1 AND KEY_2 unique but I want the vice versa as well.

Example:

KEY_1 = 111;
KEY_2 = 222;

I cannot insert this values again or neither can I insert the values vice versa.

This shouldn't be valid (since it's the same pair of keys):

KEY_1 = 222;
KEY_2 = 111;

Thanks

If `key_1` and `key_2` refer to the same thing which is what you are showing then don't make them separate entries. — Adrian Klaver, Nov 08 '21 at 18:19
https://stackoverflow.com/a/51347013/939860, https://stackoverflow.com/a/46439761/939860, https://dba.stackexchange.com/a/241079/3684, https://dba.stackexchange.com/a/187676/3684, ... — Erwin Brandstetter, Nov 08 '21 at 20:55

score 3 · Answer 1 · answered Nov 08 '21 at 18:58

3

You can achieve this with an unique functional index instead of an unique constraint.

create unique index table_name_uix 
  on table_name (greatest(key_1, key_2), least(key_1, key_2));

Details on Postgres unique constraint vs unique index here

answered Nov 08 '21 at 18:58

Stefanov.sm

11,215
2
21
21

dmg · Answer 2 · 2021-11-08T18:49:31.770

There is no simple way to do this.

UNIQUE only accepts attributes names

One way to solve this is by creating two new attributes: minatt, maxatt (so they are ordered) and then adding these two attributes as a single UNIQUE constraint.

You could also have a trigger that would update these attributes automatically (during insert/update). This way you would avoid consistency problems.

Another way to do it is by having a trigger that checks the constraint (sort of like a tuple constraint where you manually check the uniqueness of these concatenations (sounds more complicated and error prone than the UNIQUE solution).

score 0 · Answer 3 · answered Nov 08 '21 at 19:02

You can add two columns to your table, key_min and key_max, use a trigger to calculate key_min = smallest value between key_1 and key_2; key_max= largest value between key_1 and key_2 and create the constraint UNIQUE(key_min,key_max). You can see a similar solution using MariaDB and virtual columns here.

SQL unique combination of two columns and vice versa

3 Answers3