How to implement uniqueness where the order of the fields does not matter

Question

I think the following example will explain the situation best. Let's say we have the following table structure:

-------------------------------------
Member1   int      NOT NULL (FK)(PK)
Member2   int      NOT NULL (FK)(PK)
-------------------------------------
Statust   char(1)  NOT NULL

Here are the table contents for the table:

Member1    Member2    Status
----------------------------
  100        105        A

My question is how do I implement uniqueness so that the following INSERT statement will FAIL based on that one row already in the table.

INSERT status_table (Member1,Member2,Status) VALUES(105,100,'D');

Basically, I'm trying to model a relationship between two members. The Status field is the same whether we have (100,105) or (105,100).

I know I could use a before_insert and before_update trigger to check the contents in the table. But I was wondering if there was a better way to do it... Should my database model be different...

Answer 1

If you can make sure that all applications/users store the members' IDs in least-to-greatest order (the least MemberID in Member1 and the greatest in Member2), then you could simply add a Check constraint:

ALTER TABLE Status_table
  ADD CONSTRAINT Status_table_Prevent_double_pairs
    CHECK (Member1 < Member2)

If you don't want to do that or you want that extra info to be stored (because you are storing (just an example) that "member 100 invited (liked, killed, whatever) member 150" and not vice versa), then you could use @Tegiri's approach, modified a little (multiplying two big enough integers would be an overflow problem otherwise):

CREATE TABLE Status_table
( Member1 INT NOT NULL
, Member2 INT NOT NULL
, Status CHAR(1) NOT NULL
, MemberOne  AS CASE WHEN Member1 < Member2 THEN Member1 ELSE Member2 END
          --- a computed column
, MemberTwo  AS CASE WHEN Member1 < Member2 THEN Member2 ELSE Member1 END
          --- and another one
, PRIMARY KEY (Member1, Member2)
, UNIQUE (MemberOne, MemberTwo)
, ...                                    --- FOREIGN KEY details, etc 
) ;

Answer 2

Here's an alternative way to look at this. You could actually enforce the rule that the mutual relationship is always expressed by the presence of two rows, (A,B) and (B,A), instead of just one.

CREATE TABLE MutualRelationship
 (Member1 INT NOT NULL,
  Member2 INT NOT NULL,
  Status CHAR(1),
 PRIMARY KEY (Member1, Member2),
 UNIQUE (Member1, Member2, Status),
 FOREIGN KEY (Member2, Member1, Status) REFERENCES MutualRelationship (Member1, Member2, Status));

INSERT INTO MutualRelationship (Member1, Member2, Status)
VALUES
(100,105,'A'),
(105,100,'A');

Answer 3

Here an excerpt from "Symmetric Functions" in "SQL Design patterns" book you may find relevant.

Consider an inventory database of boxes

table Boxes (
   length integer,
   width  integer,
   height integer
)

Box dimensions in the real world, however, are generally not given in any specific order. The choice what dimensions becomes length, width, and height is essentially arbitrary. What if we want to identify the boxes according to their dimensions? For example, we would like to be able to tell that the box with length=1, width=2, and height=3 is the same box as the one with length=3, width=1, and height=2. Furthermore, how about declaring a unique dimensional constraint? More specifically, we won’t allow any two boxes that have the same dimensions.

An analytical mind would have no trouble recognizing that the heart of the problem is the column ordering. The values of the length, width, and height columns can be interchanged to form another legitimate record! Therefore, why don’t we introduce 3 pseudo columns, say A, B, and C such that

A ≤ B ≤ C

Then, a unique constraint on A, B, C should satisfy our requirement! It could be implemented as a function based unique index, as long as we can express A, B, C analytically in terms of length, width, height. Piece of cake: A is the greatest of length, width, height; C is the least of them, but how do we express B? Well, the answer is easy to write

B = least (greatest (length,width),
           greatest (width,height),
           greatest (height,length) )

although difficult to explain.

A mathematical perspective, as usual, clarifies a lot. Consider cubic equation

If we know the roots x1, x2, x3 then, the cubic polynomial could be factored, so that we have

Marrying both equations we express coefficients a, b, c in terms of roots x1, x2, x3

Figure 4.1: A shape of the graph of the polynomial y=(x-x1)(x-x2)(x-x3) is entirely defined by the roots x1, x2, and x3. Exchanging them doesn’t affect anything.

The functions -x1-x2-x3, x1x2+x2x3+x3x1, -x1x2x3 are symmetric. Permuting x1, x2, x3 has no effect on the values a, b, c. In other words, the order among the roots of cubic equation is irrelevant: formally, we speak of a set of roots, not a list of roots1. This is exactly the effect we want in our example with Boxes. Symmetric functions rewritten in terms of length, width, height are

length+width+height
length*width+width*height+height*length
length*width*height

Those expressions were simplified a little by leveraging the fact that the negation of a symmetric function is also symmetric.

Our last solution is strikingly similar to the earlier one, where the greatest operator plays the role of multiplication, while the least operator goes as addition. It is even possible to suggest a solution, which is a mix-in between the two

least(length,width,height)
least(length+width,width+height,height+length)
length+width+height

A reader can check that these three functions are again symmetric2. The last step is recording our solution in formal SQL

table Boxes (
   length integer,
   width  integer,
   height integer
);

create unique index b_idx on Boxes(
   length + width + height,
   length * width + width * height + height * length,
   length * width * height
);

Symmetric functions provide a basis for a nifty solution. In practice however, a problem can often be solved by schema redesign. In the box inventory database example, we don’t even need schema redesign: we can just require to change the practice of inserting unconstrained records (length,width,height), and demand that

length ≥ width ≥ height