Mysql – Finding violations of the symmetry constraint

constraintdatabase-designMySQLreferential-integrity

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?

Best Answer

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 ;
Related Question