SQL unique combination of two columns and vice versa-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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.