3

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!

0

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 – ypercubeᵀᴹ 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) – aaroncatlin Sep 5 '18 at 14:05
  • Are there only 2 types? Or more? – ypercubeᵀᴹ Sep 5 '18 at 15:55
  • There are N types. – aaroncatlin 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? – ypercubeᵀᴹ Sep 6 '18 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.