# 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   <-- 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!

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:

1. Added a Constraint so that TypeB must be greater than TypeA
2. Added a Unique Key for TypeA, RefA and TypeB
3. 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.

• I don't think this solves the problem. It would allow you to insert (10, 200) and (200, 201) - which Iif I understand correctly you don't want. to allow Sep 5 '18 at 10:41
• You wouldn't be able to link 10 to 200 if 10 was Type 1 and 200 was Type 2 as an entry will already exists where TypeA = 1, RefA = 10 and TypeB = 2 (which must be unique) Sep 5 '18 at 14:05
• Are there only 2 types? Or more? Sep 5 '18 at 15:55
• There are N types. Sep 6 '18 at 9:56
• So, do you allow both these rows? the one you have in the q: `(1, 10, 2, 201)` and this: `(2, 201, 3, 301)`. Or should the second be forbidden when the 1st exists? Sep 6 '18 at 10:01