SQL Design and Query a Table for storing Combinations

I was looking to store combinations of two fields (not permutations – order does not matter). For example, lets say I have two integer fields: A and B. I want to store the combination (1, 2). Meaning if I looked up “1”, and I should see [2], and if I lookup “2”, I should see [1].

My first guess was to create a table as follows:

CREATE TABLE RelatedIntegers (
    A INT,        
    B INT,
    PRIMARY KEY(A, B)
);
INSERT INTO RelatedIntegers VALUES (1, 2)

However, its entirely possible to store (2, 1) which would be a duplicate combination, but not permutation. Further, if I only store (1, 2), that means I have to perform a more complex query on both A and B to ensure I get all the necessary information.

The only approach I see is that I need to store each permutation, so that I can perform a quick lookup on A, and have B include whatever values I need. However, this also means I must store redundant information to ensure data integrity. If I need to store redundant information, what would be a recommended way to ensure all combinations are stored (and easily deleted)? I was thinking ensuring that the application that uses the database generates the necessary permutations on insert. The other idea I had was to use data base triggers on each insert/delete, but I imagine that might be more confusing.

Answer

You can use a check constraint to force an order of the values in the pairs.

CREATE TABLE relatedintegers
             (a integer,
              b integer,
              PRIMARY KEY (a,
                           b),
             CHECK (a < b));

That way (1, 2) can be inserted, but (2, 1) cannot. (Change < to <=, if pairs like (1, 1) are also OK. Or switch to > (or >=), if that makes more sense in your case.)

And in a query to get all the records where the pair contains a value <n> at any position, you can use IN.

SELECT *
       FROM relatedintegers
       WHERE <n> IN (a,
                     b);

Advertisement

Answer