Finding violations of the symmetry constraint

Question

Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up exactly twice in the table.

Sometimes, this constraint is violated, i.e., a pair of friends shows up only once in the table. How do I write a query that will identify all such cases (ideally, using reasonably standard SQL)? In other words, I would like the query to return the list of rows in this table, for which there is no corresponding row with the swapped fields.

An additional question: is there any way to enforce this referential integrity in MySQL?

Answer 1

To find the rows, use a left outer join:

select 
    a.Friend1ID, a.Friend2ID, b.Friend1ID, b.Friend2ID 
from
    Friends a left join Friends b 
        on (a.Friend1ID=b.Friend2ID and a.Friend2ID=b.Friend1ID)
where 
    b.friend1ID IS NULL ;

Answer 2

The simplest approach is to store each relationship exactly once, and enforce that with a check constraint Friend1

CREATE VIEW AllFriendships
AS
SELECT Friend1, Friend2 FROM Friendships
UNION ALL
SELECT Friend1 AS Friend2, Friend2 AS Friend1 FROM Friendships

If, however, you really need the table with both Friend1,Friend2 and Friend2,Friend1, you could create a self-referencing foreign key if MySql's implementation of constraints was more complete:

FOREIGN KEY(Friend1,Friend2) REFERENCES Friendships(Friend2,Friend1)

Once you have created this constraint, you will only be able to insert both rows in one statement. Unfortunately, this is does not work on MySql.