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
andTypeB
already exists (could be achieved by unique constraint) - A new row cannot be inserted where the combination of
TypeB
,RefB
andTypeA
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 <-- Type 1 and Ref 10 already exists
? | 1 | 20 | 2 | 210 <-- Type 2 and Ref 210 already exists
? | 1 | 12 | 2 | 220 <-- This row already exists exactly like this
? | 2 | 220 | 1 | 21 <-- Type 2 and Ref 220 already exists (in the B columns)
? | 2 | 300 | 1 | 13 <-- 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!
Best Answer
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:
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.