Ensuring Uniqueness Across Columns I'll lead with this: Perhaps the database design is wrong, so I will accept a re-design as a solution!

Consider the following table:

ID | TypeA | RefA | TypeB | RefB
-- + ----- + ---- + ----- + ----
3  |     1 |   10 |     2 | 200
4  |     1 |   11 |     2 | 210
5  |     1 |   12 |     2 | 220
6  |     1 |   13 |     2 | 230


The idea is that we are mapping References from an unknown number of systems. This is not a "From and To" scenario, but a two-way mapping.

We are looking for a solution that satisfied all of the below statements:


A new row cannot be inserted where the combination of TypeA, RefA and TypeB already exists (could be achieved by unique constraint)
A new row cannot be inserted where the combination of TypeB, RefB and TypeA already exists (again, could be achieved by unique constraint)
The pairs of columns must be checked both ways around


To clarify, the following rows defined below would all be invalid:

ID | TypeA | RefA | TypeB | RefB
-- + ----- + ---- + ----- + ----
?  |     1 |   10 |     2 | 201   &lt;-- Type 1 and Ref 10 already exists
?  |     1 |   20 |     2 | 210   &lt;-- Type 2 and Ref 210 already exists
?  |     1 |   12 |     2 | 220   &lt;-- This row already exists exactly like this
?  |     2 |  220 |     1 |  21   &lt;-- Type 2 and Ref 220 already exists (in the B columns)
?  |     2 |  300 |     1 |  13   &lt;-- Type 1 and Ref 13 already exists (in the A columns)


What is the best way to approach this?

Please let me know if anything is unclear in the above!
For the timebeing we have implemented the solution found here: https://stackoverflow.com/questions/11414976/unique-constraint-on-two-columns-regardless-of-order

More specifically we have:


Added a Constraint so that TypeB must be greater than TypeA
Added a Unique Key for TypeA, RefA and TypeB
Added a Unique Key for TypeB, RefB and TypeA


This is a workaround as it forces changes to the business logic to ensure the products are sorted correctly before choosing which columns to add the information to.

Leaving this answer unaccepted in the hopes of a flashier answer.