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

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

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 ) ;

The database model fails because you have two entities {Member1, Member2} which, by saying that it doesn’t matter which is which, you are saying are the same entity {Member}. In other words, you have one fact in two places, one of the cardinal sins of relational database design.

A high-level solution would be to better model the nature of the relationship. An example might be a marriage of two individuals. Rather than “Bride and Groom are married” and fussing over which gets listed first, you’d have "Marriage #xyz is between (contains) participants A and B". So, table Marriage with a primary key, table MarriageMember with foreign key to Marriage, foreign key to “Person”, and a primary key on both columns. Lets you have more than two members, which can be useful if you’re in a Heinlein story.

If you’re stuck with existing schemas (and aren’t we all), I’d require the data to be submitted with, say, the lowest value listed first, so that they are always ordered properly. You could do tricks with a checksum on the two columns as a computed column, but that wouldn’t absolutely guarantee uniqueness. But and alas, at the end of the day your model appears to be slighly flawed for your purposes.


As per the comments below, if you are modeling members that a given member is related to, then you have a "Member is related to other members" situation. Here, Member1 is the "main" member, and Member2 is an other member that "this" member is related to. (And that's the distinction needed between the two Member columns.) Thus, if a relationship is bi-directional, then you'd need two entries, to cover both "Member A is related to Member B" and "Member B is related to Member A". This, of course, would be enforced with a primary key on {Member1, Member2}, since Status appears to be irrelevant (there's only one relationship, not multiple based on status).

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');